topic Re: Pivot Table top 10 in QlikView

Pivot Table top 10

Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Pivot Table top 10

tresB — Mon, 10 Feb 2014 08:57:02 GMT

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

Re: Pivot Table top 10

Sokkorn — Mon, 10 Feb 2014 08:58:34 GMT

Hi Vincent,

Here is sample expression

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

Can you attached your app?

Regards,

Sokkorn

Re: Pivot Table top 10

luis_pimentel — Mon, 10 Feb 2014 08:58:41 GMT

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,

Re: Pivot Table top 10

Mon, 10 Feb 2014 08:59:16 GMT

try this

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

Re: Pivot Table top 10

ashwanin — Mon, 10 Feb 2014 09:07:12 GMT

HI Vincent,

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

Re: Pivot Table top 10

Mon, 10 Feb 2014 09:33:03 GMT

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!

Re: Pivot Table top 10

tresB — Mon, 10 Feb 2014 09:39:02 GMT

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)

Re: Pivot Table top 10

Mon, 10 Feb 2014 09:58:55 GMT

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.

Re: Pivot Table top 10

tresB — Mon, 10 Feb 2014 10:11:29 GMT

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

Re: Pivot Table top 10

Mon, 10 Feb 2014 10:33:30 GMT

thank you so much!

last question I now got the top 10 is it possible to put last year performance of that typical shipmentlane next to this years.

top 5 revenue 2014 revenue 2013

Re: Pivot Table top 10

tresB — Mon, 10 Feb 2014 10:57:05 GMT

You can, by using set analysis. Expression would be something like this: Sum({<Year={"$(=(Max(Year)-1))"}> }Revenue).

Re: Pivot Table top 10

Mon, 10 Feb 2014 12:15:05 GMT

thanks

Re: Pivot Table top 10

Fri, 30 May 2014 06:59:51 GMT

Good morning! friend

I am facing the same problem and I need your help

I try to display the first 10 sales per country in a Pivot table

so I added this expression in dimensions

and as a result I have

as you can see for 2010 it gives a result USA-> France-> germany and the logical order is USA-> France-> UK

why?

Thank you for your answers

Re: Pivot Table top 10

Fri, 30 May 2014 07:00:23 GMT

Good morning! friend

I am facing the same problem and I need your help

I try to display the first 10 sales per country in a Pivot table

so I added this expression in dimensions

and as a result I have

as you can see for 2010 it gives a result USA-> France-> germany and the logical order is USA-> France-> UK

why?

Thank you for your answers

Re: Pivot Table top 10

Tue, 03 Jun 2014 06:30:20 GMT

change the <=3 to <=10