Calculated field based on User Selection

Question & Answer
, , ,
1

Hi,
I am building a QS Dashboard wherein I have to give user the option to select the Week Number (From the drop-down) and accordingly the user will see the corresponding numbers. The following calculated field is used (along with its definition):

  1. Revenue CW: sumIf(amount, Year = extract(‘YYYY’, now()))
    Now, when the user selects a certain week from the drop-down, the above metric gives the desired value, but I need to build another metric which calculates the ‘Previous Week’ value depending on the user selection enabling me to calculate WoW% and other related metrics. I tried various workaround such as by creating Parameters, but to no avail. Can you please help me in creating the ‘Revenue PW’ metric?

Looking forward to hearing from the community!

Thanks

2

Hello @Erachit !

Have you looked into this solution? > https://community.amazonquicksight.com/t/how-to-create-a-week-over-week-field

If this doesn’t help, could you try breaking down the problem further or describing what you would like your end dashboard to look like?

1 Like
3

Hello @duncan ,

Thanks for your response. Please find the problem breakdown below:

Data (Revenue & Units) is available at the ‘Day’ granularity, but I need to aggregate/display the data to a weekly level. Week can be selected by the user from a dropdown list. Using this data, I need to calculate WoW% and YoY% and display them in different visualizations (such as Tables, Pie Charts, Trending YoY% Chart) in the analysis.

Currently, I am able to calculate Revenue/Units, but only for the week selected by the user with the following definition.
Revenue CW: sumIf(amount, Year = extract(‘YYYY’, now()))

Assuming, the user selects Week 25 from the drop-down, the above mentioned calculated field will display the revenue for Week 25. But, I am not able to calculate the Revenue for the previous week (i.e. Week 24, in our case) and hence not able to calculate WoW% and other relevant metrics.

Please let me know if I missed on any details.
Looking forward to the solution!

Thanks and Regards

4

Hey @Erachit ,

Have you tried creating a calculated field with the PeriodOverPeriodPercentDifference function that is based on your week choice parameter, then adding that to your WoW / YoY visualization? It might be helpful to also check out this blog post on the same topic.

1 Like