Calculating TOP 10 VALUES in PIVOT TABLE

[#1]

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.

[shree909]

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 · Accepted solution]

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.

[#4]

@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

[#5]

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

Cheers.

kv.

[#6]

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.

[#7]

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

[#8]

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

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-> UK and the logical order and it

USA-> France-> germany  why?

Thank you for your answers

[Anonymous]

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

[#10]

Hi Gysbert,

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