Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Using Rank() function with Aggr in calculated dimension could help you. See a similar post her: Re: Calculating TOP 10 VALUES in PIVOT TABLE
Using Rank() function with Aggr in calculated dimension could help you. See a similar post her: Re: Calculating TOP 10 VALUES in PIVOT TABLE
Hi Vincent,
Here is sample expression
Sum({$<ProductName = {"=Rank(Aggr(Sum(Sale),ProductName))<= 10"}>} Sale)
Can you attached your app?
Regards,
Sokkorn
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,
hi
try this
if(rank(-1*Revenue)<=10,Revenue)
HI Vincent,
Please go through the below QVW. It will solve your requirement.
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!
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)
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.
Pivot is not very sort-friendly. Said that, try this: tab ->Sort-> Expression (Copy-paste the expression from expression tab-> Descending)