Variance for calculated value in pivot between 2 dates

Q&A
, , , , ,
1

I am trying to find options to get variance between 2 dates for calculated field.

Pivot
Pivot1365×535 54.5 KB

I was able to get variance for one date with periodOverPeriodDifference({GV Impact},{snapshot_day},DAY,7), but i need for both dates.

2

Hello @nil, welcome to the Quick Sight community!

So if you are running the periodOverPeriod calculation on the pivot table and using a look-back window of 7 days, then you would need to include the previous week data in the pivot table. The only issue with this calculation is that the first date period shown would always return NULL because there is not a previous date to reference in the visual.

Something you could try as a work-around for this issue, would be to use the Lag calculation to bring the values related to the previous week on the same row as the current week. Then using manually aggregation you could run the difference calculation and reference that in your table instead. That should allow you to return the data for the first week. I’ll include documentation on the lag function below, but let me know if you have any questions.

3

Thank you for the response @WLS-DM. Let me try the option and get back :slight_smile:

4

Hello @nil, since we have not heard back from you, I will mark my previous response as the solution. Please let me know if you have any further questions on this topic and I will guide you further.