Top x % with rank

[avantime]

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!

[sunny_talwar]

Can you provide some sample data with the expected output?

[avantime]

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

[sunny_talwar]

Hahaha, is this sample data or expected output?

[avantime]

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.

[Anonymous · Accepted solution]

Sounds pretty much like this:

Recipe for a Pareto Analysis

And this:

Recipe for an ABC Analysis

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.

[vikasmahajan]

Hope this helps you

Vikas

[avantime]

Great answer, this really helped a lot, thank you very much.