Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
New Contributor III

Ignoring dimension in pivot table

Hi Guys,

I have a problem with the pivot table. I have three dimensions and a expression. I want the expression to aggregate on all the dimensions except one dimension. Please see the screenshots below.

Current Pivot Table value:

1.JPG


Expected Output:

2.JPG

Thanks in advance....

5 Replies
Highlighted
Valued Contributor II

Re: Ignoring dimension in pivot table

maybe this

sum(TOTAL <Field1, Field2>Field4)

Highlighted

Re: Ignoring dimension in pivot table

Look for TOTAL Qualifer What does the TOTAL qualifier do?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor III

Re: Ignoring dimension in pivot table

Thanks you for your reply.

We can ignore dimensions using total if we have less number of dimensions. In my real scenario, I have many dimensions, I can not include all the dimensions in total. Is it possible to do it in any other way than including the dimensions in total??? I tried using variable in total concatenating all the dimension names. But getting an error.

Highlighted
Valued Contributor II

Re: Ignoring dimension in pivot table

you can do that in a dynamic way:

i tested with this script:


MyDimensions:
LOAD * INLINE [
MyDimensions
Field1
Field2
]
;


RAW:
LOAD * INLINE [
Field1, Field2, Field3, Field4
India, AA, A, 2
India, AA, B, 3
India, AA, A, 4
India, AA, D, 5
India, AA, C, 6
India, AA, F, 6
India, AA, E, 3
US, BB, A, 7
US, BB, D, 4
US, BB, C, 2
US, BB, F, 8
US, BB, E, 9
UK, CC, A, 1
UK, CC, B, 2
UK, CC, A, 3
UK, CC, D, 5
Canada, DD, F, 4
Canada, DD, E, 5
Canada, DD, A, 6
Canada, DD, D, 9
Australia, EE, C, 8
Australia, EE, F, 5
Australia, EE, B, 6
Australia, EE, A, 7
]
;

Then i Created a variable

vDimensions

=Concat(MyDimensions, ',')

Try Expression like this

sum(TOTAL <$(vDimensions)> Field4)

Highlighted
Employee
Employee

Re: Ignoring dimension in pivot table

Maybe with

IF(Field1='Australia' and Field2='EE',

     sum(total <Field1,Field2>Expr1),

     Sum(Expr1)

)