cancel
Showing results for
Did you mean:
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:

Expected Output:

5 Replies
Specialist II

maybe this

sum(TOTAL <Field1, Field2>Field4)

Look for TOTAL Qualifer What does the TOTAL qualifier do?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Contributor III
Author

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.

Specialist II

you can do that in a dynamic way:

i tested with this script:

MyDimensions:
MyDimensions
Field1
Field2
]
;

RAW:
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
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)

Employee

Maybe with

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

sum(total <Field1,Field2>Expr1),

Sum(Expr1)

)

Community Browser