Sub Totals in Pivot Table

[anuradhaa]

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

[jonathandienst]

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

LOAD

     ....

     A,

     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.

[tresesco]

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');

[anuradhaa]

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

[anuradhaa]

is it possible to hide new Group.

[tresesco]

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.

[jonathandienst]

>>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.

[jonathandienst]

>>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.

[anuradhaa]

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