Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating TOP 10 VALUES in PIVOT TABLE

Hi people,

I have a Pivot table as

Division               Area                     Zone              Cost

London+              East+                 stratford           200

                                                    eastham          8000

                                                    Westham         510

                                                      .

                                                      .

                                                      .

                         

                         

                          West+                Epping               2000

                                                    Heathrow           788778

                                                    Harrow               4578433

                                                    .

                                                    .

                                                    .

                         

                          North+                Harrow               366987

                                                    Watford              451

                                                    .

                                                    . 

I need to calculate the top 10 values by cost in a pivot table. Can anyone help with this please.

Regards,

KV.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Add a calculated dimension instead of Zone using this expression =if(aggr(rank(sum(Cost)),Zone)<=10,Zone) and check the Suppress When Value Is Null option. If Zone and Cost are labels instead of field names, replace them with the actual field names.


talk is cheap, supply exceeds demand

View solution in original post

14 Replies
shree909
Partner - Specialist II
Partner - Specialist II

HI

if u are using qlikview version 11.

in the properties of the pivot table u have tab called Dimension limits.

in that u can specify  top 10..

in that u can enable the condition...

hope this helps

Gysbert_Wassenaar

Add a calculated dimension instead of Zone using this expression =if(aggr(rank(sum(Cost)),Zone)<=10,Zone) and check the Suppress When Value Is Null option. If Zone and Cost are labels instead of field names, replace them with the actual field names.


talk is cheap, supply exceeds demand
Not applicable
Author

@Shree

I am using QV11 and DIMENSION LIMITS is a feature available when creating charts. Its not enabled when creating a PIVOT table.

Regards,

KV

Not applicable
Author

Thanks Gysbert. Had to tweak the table a bit, but the code worked.

Cheers.

kv.

Not applicable
Author

Hi Koushik,

Their is not DIMENSION LIMITS tab for the pivot table, like other charts and straight table. You have to write it in the expression only i think.

Not applicable
Author

hi, Gysbert

                    How r u..??

I want to ask something..

Actually i have 5 yr record and want to calculate the gross sale and net sale acc. year.

but, want only three years spp. like 2005 to 2007 and i want to varriable.

so, how to get min, max year using varriable in front hand and how to get three years data or sale,but year can be any three years.

thaxs.

Regards

Anil Kumar

Not applicable
Author


Good evening friends

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

sales.PNG.png

so I added this expression in dimensions

Capture.PNG.png

and as a result I have

Capture.PNG.png

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

USA-> France-> germany  why?

Thank you for your answers

Anonymous
Not applicable
Author

Hey Gysbert.

using a pivot table also

what if in the case that wold like to display "cost" - "sales" = variance

display the line where variance is >300 or <-300? and suppress those in between.

data may look something like this

Branch             Type                  Code                         Cost                       Sale                  Variance

B1                  Type 1                5556                         1000                       1100                      100

                                                5557                          800                        1200                      400

                      Type 2                6668                          1000                       600                       -400

B2                  Type 1                5556                         1000                       1400                      400

                                                5557                          800                        1200                      400

                      Type 2                6668                          1000                       750                       -250

I would like it to display the lines where variance is >300 or <-300 how may this be achieved. thanks.

Thanks

Bon

Not applicable
Author

Hi Gysbert,

Here i need just one record whose rank is "2" either PA001 or TA001,How can i achieve it ??

Capture.PNG