topic Re: Sub Totals in Pivot Table in QlikView

Sub Totals in Pivot Table

anuradhaa — Fri, 09 Oct 2015 04:56:13 GMT

I have below pivot table.

Here i have used field A and B as dimensions.

I want to display Totals of num1 + num 2, also NumA+ NumB and Grand total in this table.

is this possible.

A	B	rec1	rec2	rec3
num1	1	15	54
num2	47	48	15
NumA	48	54	45
NumB	55	154	155

Re: Sub Totals in Pivot Table

jonathandienst — Fri, 09 Oct 2015 05:07:10 GMT

The best solution is to add a grouping field during load, like this:

LOAD

....

If(match(A, 'num1', 'num2'), 'Group1',

If(match(A, 'NumA', 'NumB'), 'Group2',

'Other') As Group,

...

(Or you can use a mapping table)

Then add Group as a dimension and use the partial totals in the pivot table to produce the subtotals.

Re: Sub Totals in Pivot Table

tresB — Fri, 09 Oct 2015 05:13:31 GMT

Create a new field using mapping like:

Map:

Mapping load * Inline [

F1, F2

num1, Group1

num2, Group1

NumA, Group2

NumB, Group2];

Load *,

ApplyMap('Map', A) as NewGroup

Inline [

A B rec1 rec2 rec3

num1 1 15 54

num2 47 48 15

NumA 48 54 45

NumB 55 154 155

] (delimiter is '\tab');

Re: Sub Totals in Pivot Table

anuradhaa — Fri, 09 Oct 2015 05:32:12 GMT

Yes, but i want to show individual raw values (num1,num2,NumA etc) in pivet table

Re: Sub Totals in Pivot Table

anuradhaa — Fri, 09 Oct 2015 05:34:38 GMT

is it possible to hide new Group.

Re: Sub Totals in Pivot Table

tresB — Fri, 09 Oct 2015 05:54:50 GMT

Then you have to generate additional records in the backend. PFA

You might want to check :How to Create a Profit and Loss Statement in QlikView for details how to handle more complex such cases.

Re: Sub Totals in Pivot Table

jonathandienst — Fri, 09 Oct 2015 06:02:17 GMT

>>Yes, but i want to show individual raw values (num1,num2,NumA etc) in pivet table

I said 'add' the group dimension. I did not say you should remove A as a dimension.

Re: Sub Totals in Pivot Table

jonathandienst — Fri, 09 Oct 2015 06:04:06 GMT

>>is it possible to hide new Group.

OK, you just asked how to add it and now your want to hide it? I am confused.

Re: Sub Totals in Pivot Table

anuradhaa — Fri, 09 Oct 2015 06:16:03 GMT

yes, i want want to display Groups in that qvw. i need only sums