How to get Top Week metric value (Top Period Values)

Question & Answer
, , ,
1

Hello, I have a pivot table with values from sales like this:

(generic demonstration)

City | 2024 W42 | 2024 W41 | 2024 W40
Chicago | 402 | 457 | 503
New York | 609 | 803 | 651

I would like to create a column that get the highest value from the row and a column that returns the week of the highest value.

For example:
City | Top Week | Top Week Value | 2024 W42 | 2024 W41 | 2024 W40
Chicago | 2024W40 | 503 | 402 | 457 | 503
New York | 2024W41 | 803 | 609 | 803 | 651

How can I create the values this way through the analysis?

It is easy using excel’s Index(column, Match(top value)) on Top Week and Max(row) on Top Week Value. But I can’t think of a way on quicksight to do it.

2

Hi @dacguilh and welcome to the QuickSight community!
Currently in QuickSight, this is a limitation as there’s no real out of the box solution for comparing fields/columns in that way.
Ideally, you would need to re-work your dataset so that your weeks was a row instead of column.

To try and compute what you’re currently doing, you would need an extensive calculated field that included every week that you’re looking to compare in this scenario. So it would look something like this, but much longer to account for all week columns:

ifelse(maxOver({2024 W42}, [{City}], PRE_AGG) > maxOver({2024 W41}, [{City}], PRE_AGG) AND maxOver({2024 W42}, [{City}], PRE_AGG) > maxOver({2024 W40}, [{City}], PRE_AGG), '2024 W42',
maxOver({2024 W41}, [{City}], PRE_AGG) > maxOver({2024 W42}, [{City}], PRE_AGG) AND maxOver({2024 W41}, [{City}], PRE_AGG) > maxOver({2024 W40}, [{City}], PRE_AGG), '2024 W41',
maxOver({2024 W40}, [{City}], PRE_AGG) > maxOver({2024 W42}, [{City}], PRE_AGG) AND maxOver({2024 W40}, [{City}], PRE_AGG) > maxOver({2024 W41}, [{City}], PRE_AGG), '2024 W40',
NULL)

This would be a nice feature to have included so I’ll mark this as a feature request to provide additional visibility to our support team.

Let me know if you have any additional questions!

1 Like
3

Thank you! Have a great day.

1 Like