Calc values in forecast scenario by referencing S#Actual, V#Trailing12MonthAvg
I have a forecast scenario S#AugFcst_7_5 with the following properties:
WF Time = 2023M7
WF start time = 2023M1
WF end time = 2024M12
# no input periods = 7
I have a Finance business rule function that is being initiated using a dashboard button in the workflow. The dashboard button calls DM Sequence/step where data units are defined as WFScenario and T#root.WFTimePeriods, business rule = FPA, function name = ttmaverage
In the function (ttmaverage) I am limiting the calculation to only the base periods of 2024 and I want it to calculate V#Periodic by looking at S#Actual, V#Trailing12MonthAvg, T#2023M6
I receive the following error when executing it:
I believe the error is because I want it to calculate off S#Actual, T#2023M6, V#Trailing12MonthAvg and that includes time periods that fall outside the range of my WF scenario AugFcst_7_5.
I tested this by changing the formula to look at S#Actual, T#2023M6, V#Trailing6MonthAvg and the calculation works but obviously does not give me what I need. If I use V#Trailing7MonthAvg or greater then it fails.
How can I calculate periodic values for S#AugFcst_7_5, 2024 base time periods from S#Actual, T#2023M6, V#Trailing12MonthAvg but still have my S#AugFcst_7_5 WF start time as 2023M1?
vkhudzey your post is not getting visibility in the forum because the forum is updated by most recent post, not most recently changed post. I know about your question because I'm part of the thread so I got an email. I never would have seen it if I were not part of the thread, so 99.9% of people don't know you responded.
If you want to get help, I suggest a new, redundant post asking the exact same question. That way everyone will see it.I ended up writing the formula in my business rule to use a combination of YTD (for the current year) plus RestOfYear (from the prior year) and then dividing by 12. This got me the same as what I would have expected to get from Trailing12MonthAverage . I also found one of the reasons is was not working was if there were time periods that had no data it would not include those periods in the calc. Example: If only 8 of the prior 12 periods had a value then it would sum the 12 periods and divide by 8 and not 12. Here is how I ended up writing my rule. In the below formula PeriodCurrYrYTD and PeriodPriorYrROY are variables I defined in the rule as follows:
= RemoveNoData(Divide((S#Actual:T#" & PeriodCurrYrYTD & ":V#YTD:O#Top: + S#Actual:T#" & PeriodPriorYrROY & ":V#RestOfYear:O#Top),12))
We have now upgraded to 8.1 and I have not tried to go back and see if Trailing12MonthAvg works.
Thank you
Denise
