Lets say I am in the month of July'23 I have to calculate moving average of past years(number of years could be dynamic) lets say 3 years for now, then the average would be of July 22, July 21, July 20.
In that case, it is needed some criteria to distinguish Actual Months from Forecast Months ( below an example with Version using Switcover Jul 23. The idea is to have for Actual months the fixed value (Value Input ACT) and the Forecast months calculated as moving averages.
Anaplan allows to reference the same Line-item in formulas without circular reference if you refer to previous months in functions like PREVIOUS, LAG, OFFSET and use the previous months' final results in the Current month calculation.
Below an example where the final result of Jul 23 (calculated as 4 year average) is taken into account when calculating Jul 24)
Hope it helps
Alex
I think you have to define item(time) -24 formula for the line item consist of time as dimension and you have to use this line item in movingsum.
Dynamically i dont think it is possible using simple logics…U have to write If-else condition and take assumption b/w lower- higher end….
The main problem here is even if I calculate using if and else for current year, I fail on future year(July'24) as it will give me circular reference
Agree…If-else condition will work only for 1 year…For 2nd Year it will not work… can we take following assumptions to get solution?
Historical data: - Min (1 year) - Max(4 year)
Forecast Periods: - Min (1 year) - Max (2 year)
If we want solution in this bound we can try something but if limits are not provided then i'm not sure how to get output
@uditbhageria I think there is no easy way to solve this really dynamically. As far as I know, there is no function to give you dynamically the same months of previous years multiple times and the only way to reference the same month of previous years is to use functions like LAG or OFFSET.
The issue is that LAG and OFFSET can reference only 1 value at the same time as there is no LOOP functionality to be applied internally to a formula and refer dynamically to multiple months.
To simulate a LOOP it is needed to add to the module a dummy list with elements like 1,2,3..etc.
Without using an additional list, the only way I found to solve your request is to have this approach:
(LAG(Value Input, 48, 0) * ( IF 'Nr. Years' >= 4 THEN 1 ELSE 0) + LAG(Value Input, 36, 0) * ( IF 'Nr. Years' >= 3 THEN 1 ELSE 0) + LAG(Value Input, 24, 0) * ( IF 'Nr. Years' >= 2 THEN 1 ELSE 0) + LAG(Value Input, 12, 0) * IF 'Nr. Years' >= 1 THEN 1 ELSE 0) / 'Nr. Years'
Hey @alexpavel, Thank you for the solution,
I need to calculate for July 24, in the same line.
THis is helpful, Thanks
I'm using the Integration API 2.0 to list workspaces https://anaplan.docs.apiary.io/#ListUserWorkspaces /workspaces "Retrieves all workspaces a user has access to within the user's default tenant." I'm only assigned to 2 workspaces (which are the only ones visible in the UI also), but this endpoint is returning everything.…
When a workspace Administrator does a model to model migration and chooses not to take the model offline, does the system still close people out of the Apps?
Would someone advise how to find the App hub. I am following the module directions but I do not see it on my end.
