Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
use below expression
=FABS(if(SecondaryDimensionality()<>1,
(sum({<Year={'2015'}>}Sales)- sum({<Year={'2014'}>}Sales)),
sum(Sales)))
SecondaryDimensionality only applies to the column dimensions of a cross-table structured pivot table. In your case it will always be zero.
but the variance value is poisitve which is supposded to be negative..
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
Thanks Jonathan,
what should be the workaround to get the desired result..
Use Dimensionality() instead of SecondaryDimensionality()
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
I am not sure what you are trying to do.
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