Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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');
Yes, but i want to show individual raw values (num1,num2,NumA etc) in pivet table
is it possible to hide new Group.
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.
>>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.
>>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.
yes, i want want to display Groups in that qvw. i need only sums