Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
anuradhaa
Partner - Creator II
Partner - Creator II

Sub Totals in Pivot Table

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.

ABrec1rec2rec3
num111554
num2474815
NumA485445
NumB55154155
8 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
tresesco
MVP
MVP

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
Partner - Creator II
Partner - Creator II
Author

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

anuradhaa
Partner - Creator II
Partner - Creator II
Author

is it possible to hide new Group.

tresesco
MVP
MVP

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
anuradhaa
Partner - Creator II
Partner - Creator II
Author

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