topic TOP 10 and Bottom 10 Customer by Revenue Amount in QlikView

TOP 10 and Bottom 10 Customer by Revenue Amount

hkg_qlik — Fri, 19 Nov 2010 00:10:24 GMT

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.

TOP 10 and Bottom 10 Customer by Revenue Amount

johnw — Fri, 19 Nov 2010 00:42:08 GMT

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.

TOP 10 and Bottom 10 Customer by Revenue Amount

hkg_qlik — Sat, 20 Nov 2010 01:18:30 GMT

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.

TOP 10 and Bottom 10 Customer by Revenue Amount

johnw — Sat, 20 Nov 2010 02:28:20 GMT

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)

TOP 10 and Bottom 10 Customer by Revenue Amount

Tue, 26 Apr 2011 14:48:15 GMT

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

Re: TOP 10 and Bottom 10 Customer by Revenue Amount

Sat, 28 Jul 2012 16:34:55 GMT

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

Re: TOP 10 and Bottom 10 Customer by Revenue Amount

khanashique — Tue, 11 Sep 2018 06:13:39 GMT

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.