I currently have an insight that calculates the top movers if any. The insight allows you to add the top X for each dimension. I would also like a rank, to rank all the values across all the dimensions that are added to the insight.
Like this:
the rank is based on the percent change.
I then want the insight to filter based on the top lowest ranks. so what would show is age group 0-18, 20-30 and Gender=F.
I basically want to show the major drivers. yes, M decreased but it was only a 2% decrease.
Hello @huda.h.yazgi, welcome to the QuickSight Community! I think I figured out the best solution for this.
Create an insight that contains 2021 avg, 2022 avg, and percentChange as your values, and AgeGroup as your Category.
Add a bottomRanked computation on the percentChange field (since largest change is negative, bottom ranked will work best for this).
Now we want to select a Block FOR expression from the Insert Code dropdown.
Also, add 2 more computations using the + Add computation and add TotalAggregate for 2021 avg and 2022 avg. In my code below TotalAggregate will represent 2021 and TotalAggregate2 will represent 2022.
Now our FOR loop will look something like this:
For Bottom.items
* **AgeGroup:** Bottom.items[index].categoryValue.formattedValue **2021 Avg:** TotalAggregate.totalAggregate.formattedValue **2022 Avg:** TotalAggregate2.totalAggregate.formattedValue **Rank:** index + 1
One thing to note from above - all of the values, including index + 1, need to be written in an expression, not plain text.
This should give you your desired output, you just might want to format it differently. As for managing AgeGroup and gender, I think you will run into some issues. You will want to split those into 2 insights.
Hi @DylanM thank you for your reply. I already have an insight for top movers. In that same insight I have top movers calculations for a few categories that I added to the field wells. Currently, there is a built in rank that selects the top X for each category in the field well. What I want to achieve in addition to that, is have it rank the values (percent difference) for all the categories and only chose the top X. Hope that makes sense.
Hello @huda.h.yazgi, I don’t think it will have a way to reference ranking amongst multiple categories, unless those options exist within the same column. The solution I gave above will return a ranking value if you are referencing the same category value. If you had a referenceCategory column that either returned an ageGroup or a gender that could be possible, otherwise it won’t work here.
