How I Built It: YTD values using a fake calendar
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!
4 -
Thanks for sharing as always Jared!
1 -
Always enjoy your posts - such a tremendous teacher! Thanks for sharing!
1 -
Thanks for sharing Jared, this is helpful.
1 -
You're a fantastic teacher @JaredDolich! Thank you so much for sharing!
1 -
JaredDolich, you are an exceptional educator! I am immensely grateful for your willingness to share your knowledge.
1 -
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.
1 -
@CommunityMember111277 really grateful for this callout - brilliant! For YTD works great and the BOOLEAN give us a lot of control on where the cumulation resets. One point I was trying make, though, was that the calculation module could be used for almost all time functions and gives us complete control how we want the calculation to function. For example, we may want a weighted moving average. The calculation module would hold all the logic in one place rather than spread out in various modules in line items.
Anyway, definitely an oversight on my part and I should have mentioned it in the essay. For a straight-up YTD, or even PTD, we certainly can use CUMULATE. Props!
2 -
I had the same thought as CommunityMember111277 going into the video about just using CUMULATE, but @JaredDolich I definitely saw the value of using your "fake time by fake time" modules for more complex time functions, so thanks for sharing!!
One point of clarification though - you mention "Using SELECT and SUM is the same as using a LOOKUP and SUM so it is not recommended, you are violating the Planual".
However, the Planual rule you were referencing (2-02-08-never-use-sum-and-lookup-together) actually makes the specific callout that:Exception:2.02-08aWhere the LOOKUP line item is a constant, the formula willoperate as a SELECT, so the performance implications are negated(emphasis added)
So SELECT and SUM is not a problem at all from a performance standpoint! And if the SELECT is done on the top level item of the list, it is also not a concern from a hardcoding standpoint ( 2-02-14-avoid-using-select ).
0
