Calc values in forecast scenario by referencing S#Actual, V#Trailing12MonthAvg

[denisefockler]

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?

[RobbSalzmann · Accepted solution]

@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.  

[RobbSalzmann]

Try changing the "UD5#SalesArea_AR" to "U5#SalesArea_AR"

[denisefockler]

I originally thought that was the issue also but it still did not work when I did that.  See below test I ran.  

I currently have as UD5# and the calculation works but only if I have V#Trailing6MonthAvg or something less than 6.

[denisefockler]

UPDATE:  my co-worker pointed me to the following post  https://community.onestreamsoftware.com/t5/Rules/Using-the-Trailing3MonthAvg-View-Member-in-a-Data-B...

I am not using data buffers but this post leads me to believe data buffers or not there is an issue with using TrailingxMon in a business rule where it can not cross years.  I will provide an update once I try the workaround in the referenced post.

[vkhudzey]

Hello,

I'm getting the same error, has anyone found any solutions without hardcoding?

Thanks in advance!

[RobbSalzmann · Accepted solution]

@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.  

[denisefockler · Accepted solution]

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

[vkhudzey]

Thanks, Denise!

This solution you propose is a great approach to solving our issue. In fact, we have tested it on version 8.2, and unfortunately, the Trailing12MonthAvg didn’t work as expected there either. It’s helpful to understand the reasons why certain functionalities stop working, especially when there are periods with no data.

Thanks again for sharing your formula!

Best regards,

Vlad.

[vkhudzey]

@RobbSalzmann  thanks Robb! I'll post it as new post then.