Sum of forecast months entries

Hi,

 

Could anyone please help me to understand, is there any way in Anaplan that we can sum the forecast months entries. 

 

For example, I have a user input dashboard which contains the future months. I have to sum the user entered values only for the forecast months. The sum should restrict within the forecast months. Currently Anaplan giving me the total sum of previous months as well as forecast months (sum of entires in a year).

 

Thanks,

Devika

Tagged:

Best Answer

  • Hi,

     

    There are many ways to do this; the choice will depend on the circumstances.  Here are some thoughts:

     

    The "Year to Go" option (check the box in Settings > Time) will give you what you want, if you can maintain the Current Period (also in Settings > Time) accordingly.  I'm not an especially big fan of this because it applies across the entire model and might consume resources/space without enough value.

     

    Another way to do this that's simple to implement is to have a separate module that shows the sum of the remaining months via formula.  This can be made to be dynamic (leveraging the Switchover) by only including months in the YTD Subtotal where a particular boolean line item is TRUE... that boolean can sit alone in a module and be set up like this:  

    Module Name:  YTD (include Time in Months and, if versions apply to your input module, include Versions)

    Line Item Name: Flag 

    Line Item Formula:  TRUE  (set Formula Scope to "All Versions except Actual")

    If the Actuals Version isn't what you are meaning to omit (i.e. you just want to omit selected previous months) then, in the simpest form, you could omit a formula and just click the months you want to include... but this implies a mainance point.  There are lots of ways this can be set up, depending on what you need.

     

    Instead of the boolean, you could also just insert a formula (in your module that shows the sum of the remaining months) that subtracts the Actuals (version) amount from the (versioned amount that uses the switchover)... which would result in netting Actuals months to zero (which could be a different answer for different versions, depending on how the switchover is set).

     

    How you present the remaining months Subtotal on the user's input dashboard is also flexible.  I will most likely be a separate module, and might be presented as a single subtotal, or as a report with many subtotals.  In any case, the calulcation will link in the inputs something like If YTD.Flag then (get data) else 0.

     

    Cheers!


    Paul

Answers

  • Hi

    Another option is to use Timesum

    https://community.anaplan.com/t5/Calculation-Functions/TIMESUM/ta-p/17474

     

    As long as you are maintaining the current period correctly in the time settings, you could automatically calculate the number of periods to calculate using a "time settings" Module.  I advocate that for many purposes (filters for actual months, forecasts months, mappings etc), and you could have a count of the forecast months to drive a dynamic sum each month.

    Timesum is useful when the target line item doesn't need a timescale.  

    As Paul said, how you present the result is very flexible

    Hope that helps

    David

  • Hi,

     

    I've had the same need numerous times and have tried different things. Using current period is good as long as your current period is the same in each version (current period cannot be defined by version). In typical situation where your previous forecast version has switchover month earlier than your current version, year-to-go (forecast months) figures would change also in the "previous version" when current period would be changed. Unfortunately there is no function for fetching the switchover month of version. That would be a great addition.

     

    As Paul noted another option is using booleans for flagging forecast months. This is good as it works correctly for versions with different switchover months. The downside of this approach is that you need duplicate number of cells for each measure.

     

    Most of the times I have ended up using timesum like David suggested (and sometimes movingsum, depending if I have timescale or not in my module) function for these kinds of purposes. In case of multiple versions, you don't probably want to hardcode time definitions to your formulas, therefore timesum requires that you have a separate module where you tell your switchover month for each version (unfortunately you have to maintain the same information in versions-menu and in the module...). Timesum also gives you the possibility to easily show averages, maximum and minimum values etc.

     

    Br, Jaakko

  • Hi David, 

     

    Thank you so much for your suggestion. I would be able to do my changes with TimeSum function.

     

    Thanks,

    Devika