Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
hkg_qlik
Creator III
Creator III

TOP 10 and Bottom 10 Customer by Revenue Amount

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

1 Solution

Accepted Solutions
johnw
MVP
MVP

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.

View solution in original post

6 Replies
johnw
MVP
MVP

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.

View solution in original post

hkg_qlik
Creator III
Creator III

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
MVP
MVP

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)

Not applicable

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

Not applicable

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

khanashique
Creator II
Creator II

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.