topic Re: Total in Pivot Table in QlikView

Total in Pivot Table

Wed, 08 Apr 2015 04:54:13 GMT

Hi,

Any idea how to create the pivot table as below?

If I used the partial sum in pivot table, it's give

Another alternative way is create the Dimension2 as expressions, then drag down to left. These then need create all the tables for budget, LY and etc and hiding behind.

Disadvantage is user couldn't export whole table to excel.

Re: Total in Pivot Table

jonathandienst — Wed, 08 Apr 2015 05:17:36 GMT

You could add 'Total' to Dimension 1:

Concatenate

LOAD 'Total' As Dimension1,

...

Autogenerate 1;

and then your expressions would need to say something like:

If(Dimension1 = 'Total', Sum(Aggr(< your original expression >, Dimension1, Dimension2)), < your original expression >)

And finally, use custom cell formatting to bold the lines with Total. Or click the + next to the expression, and enter the text style expression:

If(Dimension1 = 'Total', '<B>')

HTH

Jonathan

Re: Total in Pivot Table

danieloberbilli — Wed, 08 Apr 2015 05:21:32 GMT

You could script the 'Total' as an additional value to the Dimension1 field by calculating the sums in a seperate logical table and then join it/ or use crosstable load to create the final table....but then there is no dynamic user selection on the dashboard possible...the Totals will be rather static.

Re: Total in Pivot Table

Wed, 08 Apr 2015 05:48:30 GMT

Please find the attached document

Re: Total in Pivot Table

Wed, 08 Apr 2015 07:18:32 GMT

Thanks. Need to think a way to create that column in backend. Alright, thanks.