Custom Time Dimension / Supply Planning on a daily basis

Hi,

For a client of us we're looking into setting up a Supply Planning model where planners have the ability to plan with a telescopic view. Meaning they're able to simultaneously plan on a daily & weekly basis.

Ideally we would work with the standard time but as weekends can not be taken into account we're looking into doing this with custom time dimensions.

To do this the idea is to create a custom time hierarchy where weekdays roll up into weeks. By doing this we're conceptually already running into some technical issues.

1. How to still use time and date functions such as MOVINGSUM & TIMESUM with a custom time dimension?

2. Is there a way to use the Time Summary methods 'Opening Balance' & 'Closing Balance' with this custom made time hierarchy? This is necessary for line items such as beginning & ending inventory.

3. If we would still use the Anaplan standard time dimension, is there a clean way to consistently ignore the weekenddays in all line items on time?

Thanks in advance for your advice, any input is welcome!

If you know of any use case that has a functional Supply Planning model in days I would be excited to reach out.

Kind regards,
Hendrik

Accepted answers

All comments

JaredDolich

@HendrikDeCuyper

At first glance there might be a more simple solution.

You definitely want to set up a time system module, a module that only has the time dimension but down to the day level.

Assuming you are using a week calendar you can just use a PERIOD function to get the week the day belongs to.

From there you can add all the day properties and use a LOOKUP or SUM as appropriate.

HendrikDeCuyper

Hi Jared,

Thanks for taking the time to answer!

Unfortunately this doesn't really help us with the problem at hand, mapping between day & week as you show isn't difficult. It's rather the part about ignoring the weekdays in the standard time or using time-dependent formulas like movingsum/timesum in the custom time and making sure these have an aggregation that makes sense.

Thanks again.

Hendrik

JaredDolich

Could you use the Boolean in the system module to filter your data or use it to SUM only weekdays?

In this example I use month as the time period, but you can change that to weeks.

Then you can sum on the week period if the Boolean is TRUE.

I built this rather quickly. Best practice would be to calculate the value(name(list item)) once then reuse it.

helennie

@HendrikDeCuyper The time functions are only available with the native timescale; that's one of the tradeoffs of using a custom time dimension vs. native time.

If you need a lot of these functions and your main concern is restricting to weekdays, I would second the weekday boolean approach @JaredDolich suggested.

ArunManickam

Hello Hendrik,

I have learned a way from @DavidSmith to use the time functions when we use a custom time list.

You need to map your custom days to standard time (either to week, month or year). Using this mapping you transform your data from custom time to standard time, do the timefunctions in the standard time module and then bring the results back into custom time.

For eg, if your custom time list is having 60 days. You could map them into Year 2000 until 2059. By this way your model will not consume space as in using a daily time scale with 1 year (365 cells +)

Thanks

Arun

ToddKavanaugh

@JaredDolich What is the possibility you could share the formulas for the line items you show here? I am looking at using Day of Week as a dimension

JaredDolich

@ToddKavanaugh

You bet! Everyone needs a time system module.

The internet is super, super slow where I'm at right now so I'll give you the instructions. Once I get to a better wifi hot zone, I'll send you all the formulas and instructions.

Ok, to start, go to the best practices and scroll all the way to the bottom. I think the very first best practice ever published was on how to build a time system module by @yelena_keselman . This post actually inspired me to create my own which I reuse everywhere I go.

In most of my production applications, I have many time related system modules. Some with date properties and others with day properties like the one you're asking for.

So you'll need to make a list 0 to 6 or 1 to 7.Then make a system module that only uses that list. You can add all the day properties there like the day name, whether its a weekday or weekend, etc...

then in your date module use the Modulo function to determine if your day number. Lookup up the day name from the system module you built above and your there!

sorry I can't send the image right now but this internet is so slow I can't even get logged into Anaplan's front door. More to come...

JaredDolich

@ToddKavanaugh

Here is the blueprint of my time system module.

To get day of week using my method you will also need an additional list and corresponding system module.

I use a base date to "seed" the calendar I want to use.

Day of Week = FINDITEM(Days of Week, TEXT(MOD(Date - 'SYS036 Day Base'.Date + 'SYS036 Day Base'.Day of Week, 7)))

Day of week list (notice there are no list properties)

Day of week system module blueprint

Day of week system module

Module that seeds the date and day name

rob_marshall

@JaredDolich

Couldn't you use the WeekDay() function as well?

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/WEEKDAY.html

Rob

JaredDolich

@rob_marshall

Brilliant! I should know better! I even have a module that uses every function that's available so I should probably go back and review those.

This function would be much easier to work with but if I need to have additional properties of the day, like having a property for Mondays, I would still need to build a system module for lookups. For the weekday line item, the weekday() function would be so much easier than what I suggested. Thanks Rob!!

ToddKavanaugh

I solved what I was looking for with Weekday.. I was trying not to hard code the day names, but this is what i ended with..

IF WEEKDAY(START()) = 1 THEN Day of Week.Monday ELSE IF WEEKDAY(START()) = 2 THEN Day of Week.Tuesday ELSE IF WEEKDAY(START()) = 3 THEN Day of Week.Wednesday ELSE IF WEEKDAY(START()) = 4 THEN Day of Week.Thursday ELSE IF WEEKDAY(START()) = 5 THEN Day of Week.Friday ELSE IF WEEKDAY(START()) = 6 THEN Day of Week.Saturday ELSE IF WEEKDAY(START()) = 7 THEN Day of Week.Sunday ELSE BLANK

That gave me my day of week names from a list called Day of Week..

ToddKavanaugh

Thank you for this. I decided to go a different route but i was interested in how you went about it... Its good info..

rob_marshall

@ToddKavanaugh

I think this will be better, plus it gets you away from hardcoding in the line item as well as making it sequential with the IF statements

Create a Days list

In the DAYS system module

ToddKavanaugh

@rob_marshall

I knew there was a way to do it using the code and list. This is awesome. So much better than to use Hardcoded names.

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…