I have a report where the user can select a date period and see metrics. It contains several KPIs that show the sums of the metrics within that period.
I also want to the KPIs to calculate the percent difference compared to the previous period. Essentially, I want a calculated field that calculates the value of the previous period and use it as the target value of the KPI.
The period should be dynamic based on the date period the user has selected. So, if the user selected a week in the date filter, I want it to calculate the value of the previous week and show the difference to the current week. If the user selected a month, I want it to calculate the difference compared to the previous month.
It somewhat works when I put it in a table, select a whole month and aggregate the table by week.
But it doesn’t work when I add it to a KPI, because I get the error: Table calculation attribute references are missing in field wells and VISUAL_CALC_REFERENCE_MISSING.
It seems like this approach wouldn’t work even if the reference wasn’t missing, because the date filter would prevent the calculation from getting the previous month.
Is there a way to temporarily disable the filter for a calculation?
This seems to be a super common need and I am really surprised by how difficult it seems to be to create this type of visual.
I am probably missing something super basic, because I am new to QuickSight and I would really appreciate your help.
To do this, you need to add your date field to the “Trend Group” field well. You can choose if you want to aggregate by day, week, month, etc. The period that it uses for comparison can’t be based on the date range that your user selects. In this example, I selected one week as my date range but it’s still comparing with the value of the previous day.
Thank you so much for your response. I really appreciate it, but unfortunately this is not the desired result.
This shows the KPI value for just one day (or whatever aggregate function is selected). But of course, the KPI should show the value for the entire selected period and compare to the previous period.
This is a very basic requirement that can be easily achieved in Power Bi and Looker Studio.
Is there really no way to do this in QuickSight?
For example, in PowerBi you can create measured that calculate the currently selected max and min date, reset the user-specified filters and then calculate the value for a custom period.
Hello @nilcha, while there isn’t an easy out-of-the-box solution to this, I was able to build out a work-around solution to try and manage it. Rather than filtering by the date specifically, I was able to utilize some start and end date parameters with a time period parameter to create date groups. This allows you to show the value for something like the last 3 months and directly compare it to the 3 months previous. I created a demo version in Arena so you can see my work: Create a KPI visual that compares to the previous period
Since this is just a work-around, I will also tag this topic as a feature request. Let me know if you have any remaining questions, otherwise I can archive this question for our support team. Thank you!
Hello @nilcha, since we have not heard back with any remaining questions, I will mark my previous response as a solution since it provides a work-around option. I have also tagged this topic as a feature request and I will archive it for the support team.
If you need any further assistance with this, please post a new question in the community and include a link to this topic to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!
