Top 10 With Total

[kevbrown]

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

[avinashelite]

Hi Kevin,

try with Rank() function.

[MK_QSL]

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.

[amit_saini]

Kevin,

Use calculated dimension here like this:

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

Thanks,
AS

[amit_saini]

Also this may help you, please see the attachment.

Thanks,
AS

[#6 · Accepted solution]

Hi Kevin,

PFA. and change as my example shows Top 5.

Thanks

[kevbrown]

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.