I have been spinning my wheels on what I would think is a basic question that many business users would be asking for and I am finding it difficult to implement in Qsight.
The ask is to be able to select a date say 9/1/2024 and then select another date to compare 8/27/2024 or whatever date they choose: I have attempted to create two parameters called startdate and endate
I then filter the visual using those newly created parameters and set them equal to each other. VisitorTimeStamp = startdate OR visitorTimeStamp = Endate This then restricts the only two dates in question to compare.
Everything works great but as soon has I try to calculate the difference between those two dates unexpected and odd results start to happen please review to see where I went astray
dateDiff(${StartDate},${EndDate},“DD”) Using the same dates asforementioned I would expect to get 26 days between them but what I get is this:
I notice in the field wells it is in the sum aggregation so I moved it to the group by and I see the calculation done correctly as expected:
However, when I want to then calculate the the previous date I tried this:
periodOverPeriodLastValue(sum(VisitorCount), VisitorTimeStamp,DAY,{DateDiffTest}) in which case the last argument is highlighted red with the error: at least one argument is in the incorrect type. So tried to parseInt() which did not like it and then I tried ParseInt(toString()) nesting and that to did not work.
So to recap I am trying to calculate the difference between two date parameters and I would like to calculate dynamically that difference to be used in creating the periodtodatelastvalue OR something equivalent if that function can’t do what I want:
But what i WANT is this but without manually entering the difference:
Hello @ThreeEleven, I want to make sure I understand the result you are looking for. You want to utilize the number of days between 2 selected dates to determine the number of days ahead of a row date field that you consider for a last value calculation? The overall expectation feels a little strange, so I want to make sure I understand.
Now, if you are wanting to compare values for each of the selected dates, that is something we could manage. Using sumOver aggregations, we could get the value for the first date and second date, and calculate a difference.
First Date Visitors = sumOver(ifelse({date field} = ${StartDate}, {visitors}, NULL), [], PRE_AGG)
Second Date Visitors = sumOver(ifelse({date field} = ${EndDate}, {visitors}, NULL), [], PRE_AGG)
Now you can find the difference between these 2 fields like this:
minOver({First Date Visitors}, [], PRE_AGG) - minOver({Second Date Visitors}, [], PRE_AGG)
I hope this helps provide some detail about the issue you are facing. If you need further guidance, please provide some more detail about the expected result of the calculation and I can guide you towards a solution. It may also be beneficial to create an analysis in QuickSight Arena where we can work on a solution together. Thank you!
Hello @ThreeEleven, did my previous response help resolve the issue you are facing in QuickSight? If so, please mark my last reply as the solution. Otherwise, if you can follow-up with some more information, I can help guide you further.
If we do not hear back from you in 3 days, I will close out this topic. Thank you!
