Different partial sum in Pivot table based on dimensionality

Wed, 25 Nov 2020 16:16:04 GMT

I have a pivot table with rows zero suppressed based on below condition for second expression:

if(exp1<>0, exp2)

there are 2 dimensions and i have a requirement to write different partial sum expression based on dimensionality.

1) for partial sum on dim1, i.e dimensionality=0, it should sum up all the values that exp2 evaluates to, even when exp1=0.

2) for partial sum on dim2, i.e dimensionality=1,. it should sum up only those values that exp2 evaluates to , when exp1 <>0

Please refer to sample table image attached.

Total at level 2 should only add those exp2 values where exp1<>0 , hence 40. Total at level 1 should add up all values for exp2, even when value for exp1 =0, hence it should be 80.

Please let me know how i can achieve this

Re: Different partial sum in Pivot table based on dimensionality

swuehl — Wed, 01 Jun 2016 14:07:58 GMT

Maybe something like

=If(Dimensionality() = 0,

Sum(Aggr( YourExpression2, Dim1, Dim2)),

Sum(Aggr( If( YourExpression1 <>0, YourExpression2), Dim1, Dim2))

)

Re: Different partial sum in Pivot table based on dimensionality

Thu, 02 Jun 2016 01:59:34 GMT

Thanks!that worked.

topic Re: Different partial sum in Pivot table based on dimensionality in QlikView

Different partial sum in Pivot table based on dimensionality

Re: Different partial sum in Pivot table based on dimensionality

Re: Different partial sum in Pivot table based on dimensionality