calculating variance in pivot table with secondary dimensionality showing -ve values

[shree909]

Hi folks,

i have a requirement where i want to calculate the variance in pivot table.

the issue i have is when i am using the formula as

=if(SecondaryDimensionality()<>1,

  (sum({<Year={'2015'}>}Sales)- sum({<Year={'2014'}>}Sales)),

  sum(Sales))

for the year 2014 its showing the -ve values..

i want the -ve sign only when the difference is -ve..

PFA sample application

Thanks

[MK_QSL]

use below expression

=FABS(if(SecondaryDimensionality()<>1,

  (sum({<Year={'2015'}>}Sales)- sum({<Year={'2014'}>}Sales)),

  sum(Sales)))

[jonathandienst]

SecondaryDimensionality only applies to the column dimensions of a cross-table structured pivot table. In your case it will always be zero.

[shree909]

but the variance value is poisitve which is supposded to be negative..

[#5 · Accepted solution]

Try below expression.

=if(Dimensionality()<>1,

  (sum({<Year={'2015'}>}Sales)- sum({<Year={'2014'}>}Sales)),

  sum({<Year={2015,2014}>}Sales))

I just added set analysis conditions for Sum(Sales) also to display values for 2014 and 2015 years only.

Regards,

KKR

[shree909]

Thanks Jonathan,

what should be the workaround to get the desired result..

[MK_QSL]

Use Dimensionality() instead of SecondaryDimensionality()

[#8]

shree909‌ , were you able to resolve the issue? If yes, please post the resolution and mark any replies appropriately if they helped in the resolution.

Regards,

KKR

[jonathandienst]

I am not sure what you are trying to do.

[#10]

When dimensionality() is not equal to 1 (for Totals), he want to calculate Sales Variance between 2015 and 2014. For other values, he want to calculate Sum(sales).

Regards,

KKR