topic Re: Top 10 With Total in QlikView

Top 10 With Total

kevbrown — Wed, 13 Aug 2014 10:49:09 GMT

I have a straight table with a sum expression. I only want to show the top 10 and need a total on the bottom line for the top 10. I can select the top 10 but it gives me the total for all 100.

Any suggestions?

Kev

Re: Top 10 With Total

avinashelite — Wed, 13 Aug 2014 10:52:44 GMT

Hi Kevin,

try with Rank() function.

Re: Top 10 With Total

MK_QSL — Wed, 13 Aug 2014 10:52:59 GMT

Consider you have below table

Load * Inline

[

Customer, Sales

A, 100

B, 120

C, 75

D, 90

E, 120

F, 140

G, 120

H, 200

I, 240

J, 110

];

Create a Straight Table

Dimension

Customer

Expression

SUM({<Customer = {"=Rank(SUM(Sales),4)<=5"}>}Sales)

This will give you TOP 5... Replace 5 with 10 to get TOP 10 as per your database.

Re: Top 10 With Total

amit_saini — Wed, 13 Aug 2014 10:53:09 GMT

Kevin,

Use calculated dimension here like this:

=aggr(if(rank(sum(Data))<=10, User),User)

Thanks,
AS

Re: Top 10 With Total

amit_saini — Wed, 13 Aug 2014 10:54:47 GMT

Also this may help you, please see the attachment.

Thanks,
AS

Re: Top 10 With Total

Wed, 13 Aug 2014 11:24:54 GMT

Hi Kevin,

PFA. and change as my example shows Top 5.

Thanks

Re: Top 10 With Total

kevbrown — Wed, 13 Aug 2014 11:33:59 GMT

That works a treat thanks, however, what if i now want to add a second dimension, so in your example, add office but still show only the top 5 salemen.