Pivot Table Top 5

[#1]

Hi,

I have a Problem with Pivot Tables to get a Top N List.

Problemdescription:
This is only an example: I have a Pivot Table sorted through Sum(Quantity). Now I would like to create a Top 5 List with Pivot Table. I know that I can do this with Straight Tables in the Presentation Tab, but I need it for Pivot Tables.

Prefered Solution:
I would like to get a Pivot Table for the Top 5 Product sorted by Sum(Quantity). If it is possible please without Macros.

For a solution please use my attached .qvw file.

Thank you for helping me.

Regards,

Herbert

[#2 · Accepted solution]

I hope the aggr and the rank funtion will help you, just replace the Productname dimension by :

=if(aggr(rank(SUM(Quantity),4,0),Productname)<=5,Productname)

and check "remove when value is null"

[#3]

Hi Herbert

The solution is to use a combination of the Aggr and Rank functions in the dimension, see the attached which is your original file with the extra pivot table solution.

Regards,

Nigel.

[#4]

Thanks for the 2 solutions, the combination of the Aggr and Rank functions works fine for me.

Have a nice day.

Regards,

Herbert

[#5]

Greetings to you,

I have got a problem with license,

When I run your Pivot Table Top 5.qvm file, I see this message :

Access denied.
The file was created by a licensed QlikView user and since you are using a QlikView Personal Edition you may not open this file.

Could i possibly ask you to tell me, what shall i do?

Thanking you,

[#6]

I want to limit my pivot table to a rolling 12 months only.  How would you limit a pivot table without using filters to just the past 12 months