topic Re: total in pivot table..(disregarding calculated dimension) in QlikView

total in pivot table..(disregarding calculated dimension)

israrkhan — Thu, 28 May 2015 13:34:24 GMT

Hi Guys,

I have a Pivot table,with two dimensions

1)first dimension is Brand (it is calculated dimension, i am showing top 8 Brands)

2) second dimension is YearWeek, which is dragged horizontally, and expression is sum(sale)

problem is, i have more than hundred Brands, in the Total i want to show the Total of all Brands...

but when i enable "Show Partial Sum" it shows me sum of only 8 rows(8 brands)

how to show total of all brands,not for only top 8...

any idea....

many thanks...

awhitfield — Thu, 28 May 2015 13:47:56 GMT

Hi israrKhan

can you please upload an example qvw?

Andy

israrkhan — Thu, 28 May 2015 14:10:04 GMT

find the attached sample file...

did you get what i want to do?

Thu, 28 May 2015 15:13:40 GMT

Hi Israr,

show sum will compute data displayed in the pivot table.

what you can do is to replace the null() value in your calculated dimension by 'Others'.

This will create a new extra line gathering others data and your total will be fine

Chrs

stigchel — Thu, 28 May 2015 15:34:03 GMT

The others is a good option, should you not want that you can use the field MODEL as Dimension and do the limitation in the expression like this

=if(Dimensionality()=0,

Sum(Amount),

If(Aggr(NODISTINCT Rank(Sum(TOTAL <MODEL> Amount)), MODEL)<=10,

Sum(Amount)

))

See also attached qvw, the copy of the table below

israrkhan — Thu, 28 May 2015 16:20:05 GMT

Thank you Guys...

Really Helpful...............!!!

israrkhan — Thu, 28 May 2015 16:23:40 GMT

but in this case,,it will show me others as total..

it will not add top 8 into that total? am i right...?