topic Re: Ignoring dimension in pivot table in QlikView

Ignoring dimension in pivot table

avinashkk696 — Wed, 25 Nov 2020 16:16:04 GMT

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:

Expected Output:

Thanks in advance....

Re: Ignoring dimension in pivot table

zhadrakas — Wed, 10 Jan 2018 07:25:26 GMT

maybe this

sum(TOTAL <Field1, Field2>Field4)

Re: Ignoring dimension in pivot table

Anil_Babu_Samineni — Wed, 10 Jan 2018 07:25:49 GMT

Look for TOTAL Qualifer What does the TOTAL qualifier do?

Re: Ignoring dimension in pivot table

avinashkk696 — Wed, 10 Jan 2018 07:45:06 GMT

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.

Re: Ignoring dimension in pivot table

zhadrakas — Wed, 10 Jan 2018 08:14:04 GMT

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)

Re: Ignoring dimension in pivot table

Clever_Anjos — Wed, 10 Jan 2018 09:42:18 GMT

Maybe with

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

sum(total <Field1,Field2>Expr1),

Sum(Expr1)

)