How I Built It: YTD values using a fake calendar

Options

Author: Jared Dolich is a Certified Master Anaplanner with 8 years architecting Anaplan models.

Anaplan Champions! A colleague asked me recently how I might obtain a YTD value using a fake calendar. Yeah, I know — I should have asked why they couldn’t use the normal time hierarchy, but there are occasions where you are confronted with a user story that won’t conform to a built-in calendar. For example, hourly planning, limited time ranges that don’t require a whole year, and non-conforming 454 calendars.

My inclination is to use a calculation module, as part of DISCO. So in this case you will need to create another list similar to the fake calendar so we can go up/down the list as we would with a normal time calendar. This method can be used to create pretty much all the time functions.

In this ‘How I Built It’ tutorial, I walk through how to achieve this. If you prefer step-by-step instructions, I’ve included that below the video.

Conclusion

Using this method you can create most, if not all, the time functions. While it may not seem intuitive, the DISCO usage allows us to architect this solution without burdening the calculation engine. The calendars when combined with your planning lists may create large modules. Be mindful of the sparsity. I’m sure there are more efficient methods. How might you make this more optimal?

Leave a comment!

Step-by-step instructions can be found below.

………………

First off, the final result will look like this:

So we have two lists. You'll need to use the CODE in your fake list so we can convert it to numbers. Then create another list that uses the same numbers, like so:

Fake Calendar List - Notice how CODE is being used

Surrogate Fake Calendar List

ALWAYS build a system module for every list you create. Here's mine for the two lists.

Now build a system module that combines these two lists and add a line item that gives us the YTD valid cells.

Now build a CALC module that includes both lists AND your lists used for calculating YTD. In this case I have a product list I added. Notice the values we want are on the right column (FT Total)!

A second CALC module can be used to obtain just the right column because we don't need all the detail.

Finally, we put it all together in our planning module. I've included a method to also show you how to obtain the previous month value too.

Conclusion

Using this method you can create most, if not all, the time functions. While it may not seem intuitive, the DISCO usage allows us to architect this solution without burdening the calculation engine. The calendars when combined with your planning lists may create large modules. Be mindful of the sparsity.

Comments

  • Hello Anaplan Champions! Just wanted to mention if you're a video-type-learner you'll notice I don't walk you through the formulas. Deliberate - wanted to keep the video as short as possible. But there's good news! Below the video is the documentation which I show you all the formulas.

    I use this strategy for nearly all my retail clients. For example, hourly planning, limited time ranges that don’t require a whole year, promotional planning, and non-conforming 454 calendars.

    Enjoy! Keep on, keepin' on. Thank you @GingerAnderson and @becky.leung for considering this post for publication!

  • jwu
    Options

    Thanks for sharing as always Jared!

  • Always enjoy your posts - such a tremendous teacher! Thanks for sharing!

  • Monpero
    Options

    Thanks for sharing Jared, this is helpful.

  • You're a fantastic teacher @JaredDolich! Thank you so much for sharing!

  • Khadar
    Options

    JaredDolich, you are an exceptional educator! I am immensely grateful for your willingness to share your knowledge.

  • Hi @JaredDolich ,

    I think we can get the same output using CUMULATE function as well without creating all these calc modules separately. Just wanted to check my understanding.