Dear, I have two questions regarding an implementation I'm currently working on. To give a little background information, it is a financial planning model for the coming 3 years and has a budget version and 4 different forecast versions. My questions are the following: [list=1]
Copy data from one month into all months
I have a module in which I have data in February
I need to be able to copy the data I have in February to all the remaining months of the year
(since this needs to happen every year, I cannot simply hardcode it as such '....'[SELECT:TIME.Feb 17] as this would only be valid for 2017 and not 2018
Did anyone encounter the same issue and has a solution for this?
Summarize periodic data to YTD data
In another module of the model, I have periodic data from March to December.
I need to somehow make this Year to date data, meaning that the value of:
March = March
April = March + April
May = March + April + May
June = March + April + May + June
Anyone any experience on this?
Should the explanations not be sufficiently clear, don't hesitate to ask for further clarifications. Thanks for your help! Kind regards, Albrecht
Thanks for the clarification. I may still have a couple of questions but I'll outline a general approach which could help - but you might need to adapt it if I've not understood something!
Option A - have you considered using the Actual version for this? You would import your data into your 'Actuals' version and use a Switchover month to apply a formula for all months after the switchover month. It would then use your actuals for all months prior to the switchover date. This is the most usual way of doing this.
So if you set your switchover date to March 17 on each of your forecasts then Anaplan will use actuals for Jan 17 and Feb 17 and then your formula from March 17 onwards. If you use the formula PREVIOUS(x) on your line item then it will return the previous month's value i.e. in March 17 it will return Feb 17 actual value and then every month thereafter will be the same. Next year you just need to change the switchover date for the forecast versions to March 18 and the same will apply for 2018.
Option B - or you could do something similar to this:
1. Your original line item would be for import only (let's call it 'Actual Data')
2. Create a boolean line item that is ticked to true (you could select this manually or perhaps create a formula to work it out): 'Select Switchover Month'
3. Create a time formatted line item to return the switchover month and make sure that Summary for Time is 'Last Non Blank' e.g. 'Switchover Month' = IF Select Switchover Month THEN ITEM(TIME) ELSE BLANK
4. Refer to this in your formula for a fourth line iteme.g. Calc = IF 'Select Switchover Month' THEN Actual Data ELSE IF ITEM(Time) > 'Switchover Month'[SELECT: TIME.All Periods] THEN PREVIOUS(Calc) ELSE 0
Does that help at all?
We have developed something similar in our current project.
1) Have seperate module to load actual numbers into them
2) Have a forecast module
3) Have a running forecast module where we use the formula IFACTUAL THEN module 1 else module 2
4) Then use the YTD Funciton