I would like to know how to use percentileRank or rank function for column B within the visualisation after another rank function that is derived from column A is being applied as the filter. The ranking in column B should be based on the prefiltered list from column A.
E.g.
I have a use case that I have 100 rows of data which contains
country
year month
customer
total revenue
no of items sold
My aim is to firstly filter the top 20 customers by total revenue.
Then I will need to apply conditional formatting for both total revenue and no of items sold
Condition: the top 40% of the customers (i.e. Rank 1-8) as green and the others as blue.
The difficult part is to not only ranking total revenue but also no of items sold has to be ranked within these top 20 customers by total revenue.
I have tried using top and bottom filter to replace the top 20 ranking filter but it did not work since a customer could be present in more than one country. The only solution is to rank them by partitioning both country and year month.
Is it solvable in QS or do I have to use window function to rank the customers based on total revenue in dataset level?
Hello @kkc818, I was discussing this with @Brett and we both put together some Arena demos to handle the situation. I included Country and Date Month partitions into the rank calculations whereas he didn’t so I thought both would be helpful for you to find a working solution.
In short, I created a denseRank for customers based on their profit each month to retrieve the revenue top 20. I then applied a filter to only show customers within the top 20. Then, I created a calculated field to only return Quantity of Products sold when the Revenue Rank is less than or equal to 20.
This calculated field was used in the 2nd denseRank calculation to determine the rank for number of products sold within the top 20 by revenue.
Hi @kkc818,
I put together a slightly different approach in Arena as well for you to compare. Let us know if one of these options helps achieve your desired end goal or if you have any questions.
Depending on how you’re looking to incorporate country and date (do you want the sales aggregated by country and date or do you just want that information included in your visual), this can be adjusted to incorporate other fields in your rank aggregation.
But ultimately, you’ll want to setup some type of rank calculated field so that you can base your conditional formatting off of those fields like in my example.
Hello @kkc818, since we have not heard back from you, I will mark my previous response as the solution. Please let us know if you still need assistance with this topic, and we can guide you further. Thank you!