Copy month data

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 
Tagged:

Answers

  • Hi Albrecht

    1. Could you provide a bit more info about the use case?
    • My understanding is that you want to copy Feb 17 data into Mar-Dec 17 and Feb 18 data into Mar-Dec 18 - is that right? 
    • Would it always be February?
    • For all years in the model?
    • Where does the Feb data come from - is it Actuals that are imported/manually entered or is it the result of a calculation?
    2. Could you use the YEARTODATE function?
    https://community.anaplan.com/anapedia/calculation-functions/yeartodate

    Regards
    Kirsty
  • Kirsty, 

    Thank you for your reply. Please find below my responses

    [list=1]
    To all your questions 'Yes' and it is Actual data that I copy to the forecast versions (so no result of a calculation, but of an import process)
    YEARTODATE function works perfectly

    Thanks for your help so far!. 

    Kind regards, 
    Albrecht 
  • Hi 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.

    See:
    https://community.anaplan.com/anapedia/around-the-platform/settings-tab/versions#WorkActualForecastVersions

    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?
    Regards
    Kirsty
  • Hi Albrecth

    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
  • Dear Kirsty, Harish, 

    First of all many thanks for your time and efforts to help me out on this one. I've had another look at this issue over the weekend and found a really easy solution:[list=1]
    Create line item to copy actuals into forecast versions (via import process)
    Create line item to copy Feb data to all next months via following formula:
    • IF MONTH(START()) > 2 THEN LAG('Copied amount to RAF (YTD)', MONTH(START()) - 2, 0)
    I sincerely appreciate (and want to thank you) for your time and thinking along with me for a solution to this problem!

    Kind regards,
    Albrecht 
  • Hi everyone!

    We have moved this thread to the Model Building Q&A area. 

    Thank you!