YTD Workaround?

BrentOrr
BrentOrr
in Modeling

I have an issue with the way Anaplan calculates YTD and need help figuring out a workaround.  The YTD value in Anaplan includes the current week, which is NOT how I would like it or need it.  i am in a weekly system and do not have actuals for the week I am in.  So when comparing YTD Actuals against YTD Forecast it is always wrong and always undercounting the YTD Actual by the current week.  I first submitted this as a bug only to be told this was the way it was designed.  So I need a way to compare YTD Actuals to YTD - Current Week Forecast.  They would both be YTD - Current Week.

Tagged:

Best Answer

  • PaulRitner
    PaulRitner
    Answer ✓

    Hi, 

     

    I'm flying blind here, but there are other ways to aggregate across time without using the YTD function.

     

    One way is to have a line item that aggregates another value like this:

    Line item 1 is the weekly source values

    Line Item 2 is the YTD value, containing a formula like:  Previous(Line Item 2)+Line Item 1  (if you have more than 1 year defined, you'll have to reset the clock in week 1)

     

    If the Line Item 2 value is used in a report that doesn't contain time, you might have a selector that chooses which week to present, or the report can contain YTD values for all weeks, and have time as part of the report module... I prefer the latter, it allows users to look back in time.

     

    If this doesn't help, please share some screen shots and we can take a deeper dive.

     

    Paul

Answers

  • Jaakko
    Jaakko

    Hi,

     

    I thought about this for a while and with week time scale Paul's solution with previous function is probably the simplest . With month time scale I have used the following solution.

     

    Formula in separate YTD Line item:

    YTD = MOVINGSUM(Source Line Item, -1 * MONTH(ITEM(Time)), 0).

    If you don't want to include the last time member, last parameter would be -1 instead of 0.

     

    I guess there is no function for week numbers in the same way than for months (MONTH-function above)? In that case for this solution you'd need a separate line item (for example in time assumptions module) where you'd calculate the week number for each week. This could be done, for example, with cumulate(1) function and resetting that in the first week of year.  Or you could try to parse it from the time item name, but I am not sure if that is wise. 🙂 Then it would look this confusing:

    Week Number =

    VALUE(RIGHT(LEFT(NAME(ITEM(Time)), FIND(" ", NAME(ITEM(Time)), FIND(" ", NAME(ITEM(Time))) + 1)), 3))

     

     

    And weekly YTD without the last member would look like this:

    YTD = MOVINGSUM(Source Line Item, -1 * Time Assumptions.Week Number, -1).

     

     

    Br,

    Jaakko

  • andrewtye
    andrewtye

    How about a separate module that does the summarising up and using the TIMESUM formula... I have the same issue and generally don't use the pre-calculating such as YTD / YTG.

    So the formula would be TIMESUM([Original Line Item], [Start Week], [End Week]) - with the Start / End week being in a separate module.

    Particularly useful if you've got some non-standard roll-ups of time or want say last n weeks.

Categories