TOP 10 and Bottom 10 Customer by Revenue Amount

[hkg_qlik]

Hi,

I want to calculate both top 10 and bottom 10 customer based on sum of Revenue Amount for each customer.

Both top 10 and bottom 10 customers should be reflected in a Table as well as PIE and BAR chart.

Thanks.

H

[johnw · Accepted solution]

Attached is a solution using a calculated dimension:

=aggr(if(rank(sum(Revenue))<=10 or rank(-sum(Revenue))<=10,Customer),Customer)

And perhaps I'm exceeding my role here, but I STRONGLY recommend that you don't use a pie chart like this. The main point of a pie chart (if you must use one at all), is to show a part-to-whole relationship. The assumption when you see a pie chart is that the whole thing is 100%. If you exclude all of the customers that aren't in the top or bottom 10, you are not showing a part to whole relationship, the pie chart is not 100%, and it is even more useless than pie charts normally are.

[hkg_qlik]

I don't understand why isn't its working for me. I would like to make it more clear.

For Example: I have a straight Chart table with column names in the following order:

1. Customer Name

2. Revenue Amount

3. COGS Amount

4. Profit

Is there any way I could get top and bottom 10, 25, 50 and 100 customer by revenue. Also is there any way where I can right click on the table and change the properties to reflect the top customer?

Thanks.

H

[johnw]

I'm not sure I understand, but I've added columns for Profit and COGS to the straight table. I've also added a list box for new field TopN. If you select a value, it will give you that number of top and bottom customers. If you don't select a value (or select multiple values), it gives you all customers. It's using this calculated dimension:

=aggr(if(len(TopN)=0 or rank(sum(Revenue))<=TopN or rank(-sum(Revenue))<=TopN,Customer),Customer)

[#5]

John,

Your solution works fine. but it fails when there is 0 value. Rank function considers 0 value but aggr function omits 0 values giving wrong result. In other words if for Customer M, revenue is 0 then, M will not appear in TOP10 or BOTTOM 10 giving 1 less record.

Do you have any work around for this? Any help is greatly appreciated..

Thanks

[#6]

Sir make a Input box and define the  drop down value so u can find easily find the top customers...

[khanashique]

Hi John,

I have similar query to show top 5   and bottom 5 customers together, how can i define the calculated measure for master item (Dimension)

aggr(if(rank(sum(Sales))<=10 or rank(-sum(Sales))<=10,Customer),Customer)

Thanks,

MAK.