Hi All,
I’m working on adding Year-over-Year (YoY) percentage calculations to a QuickSight visual. The visual includes both amount ($) and count (#) metrics, with year and month as columns. Both metrics are based on calculated fields.
The amount ($) metric, which uses a sumIf function, is working correctly with the YoY calculation. However, the count (#) metric, which uses a distinct_countIf function, is only showing YoY values for individual months. When looking at the year level, it shows null values.
Both YoY calculations are using the percentDifference function.
Can you help me understand why the count metric isn’t showing YoY percentages at the year level, and suggest a way to resolve this issue? Let me know if you need any additional information.
Thanks!
Hi @Iamuktha,
Thanks for posting your question and welcome to the QuickSight community.
To illustrate the behaviour I see, I’ve attempted to recreate your calculations using a typical sales dataset and display these using a table visual.
In my case I have a Profit measure, for which I have 3 calculated fields:
cfAmountMetric - sumIf(Profit,Region="EMEA")
cfAmountYoY - periodOverPeriodPercentDifference(cfAmountMetric, {Order Date}, YEAR, 1)
cfAmountMoM - periodOverPeriodPercentDifference(cfAmountMetric, {Order Date}, MONTH, 1)
Then I have a count of Order IDs, for which I have 3 more calculated fields:
cfCountMetric - distinct_countIf({Order ID},Region="EMEA")
cfAmountYoY - periodOverPeriodPercentDifference(cfCountMetric, {Order Date}, YEAR, 1)
cfAmountMoM - periodOverPeriodPercentDifference(cfCountMetric, {Order Date}, MONTH, 1)
I then added all of the related fields and calculated fields to two table visuals, in the first, the Order Date is aggregated on Year:
In my example the first year is (as expected) missing YoY values for the first year and MoM for all years.
In the second table I aggregated Order Date on Month:
In this example, the first month is (as expected) missing MoM values and all months in the first year are missing YoY values.
If you can compare your data, calculated fields and visuals to these, and share your results, then perhaps we can identify how to achieve the desired results. Perhaps you could also share what you see vs what you expect?
Many Thanks,
Andrew
Hi @Iamuktha
It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.
If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.
Thank you!
Hi @Iamuktha
Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.
Thank you!