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
Hi all, The Client ID and Client Secret are available, intended for uploading and downloading files. Could anyone advise how to use these credentials DIRECTLY to make API requests? Thank you!
At Uphold customer support ⭐+1-888-355-2348 accounts may be temporarily disabled or restricted for various reasons. Raise a concern on priority basis at Uphold support ⭐+1-888-355-2348 if you notice any suspected malicious activity, or a problem during account recovery. To contact Uphold support directly at…
I have A6 as the parent list and two sibling lists: A6.5 and A7. The A7 list includes an attribute that maps to A6.5. In the UX, the user will select A6.5 using a content selector, and the grid (dimensioned by A7) should display only the A7 items associated with that selected A6.5. Since the selector is a page context and…
