Pivot Table top 10

[#1]

Hi all,

I was just wondering if its possible to let a pivot table show me the top 10 best products I got.

Now I am using the following dimensions:

Productname:

Year

Month

Expression:

Revenue

Would using a variable solve my problem? Because when using a straight table you can use the dimension limits for a top10.

However I really like the benefits of the pivot table to show my values.

*Update

I would also like to add a slider attached to the table. So I could show top 10/15/20/25/30

kind regards.

[tresesco · Accepted solution]

Using Rank() function with Aggr in calculated dimension could help you. See a similar post her: Re: Calculating TOP 10 VALUES in PIVOT TABLE

[Sokkorn]

Hi Vincent,

Here is sample expression

Sum({$<ProductName = {"=Rank(Aggr(Sum(Sale),ProductName))<= 10"}>} Sale)

Can you attached your app?

Regards,

Sokkorn

[luis_pimentel]

Hi Vincent,

You might use the rank function in your expression. Something like:

=sum(aggr(if(rank(sum(Revenue))<=10,Revenue), Year,Month))

kind regards,

[#5]

hi

try this

if(rank(-1*Revenue)<=10,Revenue)

[ashwanin]

HI Vincent,

Please go through the below QVW. It will solve your requirement.

[#7]

Hi guys,

I got the top10 with this one in my dimensions:

=if(aggr(rank(sum(LaneStatistics.Amount)),LaneStatistics.ShipmentLane)<=10,LaneStatistics.ShipmentLane)

Now i would like to add:

- A slider where i can switch to top 15/20/25

- The top 10 descending from 1 to 10 (now they are random)

- And subtotals

I can figure out some of the points myself but it probably takes me more time and its probably really easy.

Thanks in advance!

[tresesco]

Declare a variable; store the slider value in that variable; then use the variable in the expression instead of hardcoding the rank, like:

=if(aggr(rank(sum(LaneStatistics.Amount)),LaneStatistics.ShipmentLane)<=$(VariableName),LaneStatistics.ShipmentLane)

[#9]

Yes the variable is working. Thank you!

You maybe also know the anwser how to sort the values?

In my top 5 it comes up as

500

900

300

505

100

I already got the subtotals added this is the only problem thats left.

[tresesco]

Pivot is not very sort-friendly. Said that, try this: tab ->Sort-> Expression (Copy-paste the expression from expression tab-> Descending)