Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a request that sounds like this.. We need a table that shows:
-top 20% (out of the total number of rank A clients) rank A clients sorted by total sales.
-top 30% (out of the total number of rank B clients) rank B clients sorted by total sales.
-top 50% (out of the total number of rank C clients) rank C clients sorted by total sales.
Any thoughts on how I should approach this?
Thanks!
Sounds pretty much like this:
And this:
For rank you need to use Aggregate:
https://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/ChartFunctions/aggr.htm
I had to come up with the expression which calculates the rank recently and it's something like this:
aggr(rank(sum({<Customer.Name= >} sales)), Customer.Name)
Unfortunately I can't share the whole solution.
Can you provide some sample data with the expected output?
Sure, it should look something like:
Client | Client rank | Turnover |
Client2321321 | A | 99999 |
Client3123 | A | 99998 |
Client32432 | B | 999 |
Client342323 | B | 998 |
Client344335 | B | 997 |
Client2454 | B | 996 |
Client84332311 | B | 995 |
Client946743 | C | 99 |
Client32420 | C | 98 |
Hahaha, is this sample data or expected output?
This is the expected output, as you asked.
I have 2 issues.
- how to get the top x% of clients with the same rank.
- how to show them in a table, one after another, sorted by rank and turnover.
Sounds pretty much like this:
And this:
For rank you need to use Aggregate:
https://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/ChartFunctions/aggr.htm
I had to come up with the expression which calculates the rank recently and it's something like this:
aggr(rank(sum({<Customer.Name= >} sales)), Customer.Name)
Unfortunately I can't share the whole solution.
Hope this helps you
Vikas
Great answer, this really helped a lot, thank you very much.