I am trying to calculate Average Percent Period Movement (AVPPM) — i.e.:
AVPPM=Actuals(Current)−Actuals(Prior Period) / ∣Actuals(Prior Period)∣×100 = and store the result in S#AVPPM for the current POV.
I tried using the below script in Business rule or Scenario member formula for S#AVPPM but not getting the correct result.
api.Data.Calculate("S#AVPPM = (S#Actuals - (S#Actuals:T#POVPrior1)) / (ABS(S#Actuals:T#POVPrior1))*100", True)
Appreciate any help!
If you are showing the entire script, then part of your problem is that you have selected to write Durable data, which normally requires a preceding ClearCalculatedData() to get rid of previously calculated Durable data.
Though not likely the cause of the unexpected results, you are not performing any divide by zero checking. Either use the Divide(numerator,denominator) function within the Calculate() formula, or pre-get the denominator into a databuffer with RemoveZeros() and reference the resulting buffer in the two spots in your existing Caclulate().
However, that all said, unless you have a really good business reason why you would in effect be copying/storing all your actual data into a seperate Scenario that then needs calculating every time someone wants to see correct numbers, I personally would have written this as a UD8 Dynamic Calc, so you are only paying price when someone actually surfaces this Dym Calced UD8 member in a Cube View.
I would create an account called AVPPM - Average Percent Period Movement and calculate it there, or you can create it in UD8 for a truly dynamic calc metric. Then you can calc it for any scenario, including Actual
