Use fake week to replace default week calendar

KayK
KayK
edited February 28 in Best Practices

Author: Kay Kuang is a Certified Master Anaplanner and Principal Data and Insights Architect at Anaplan.

Some businesses run their planning or operations by weeks. However, setting the Anaplan model default Calendar Type to Week format can be inconvenient. For example, converting dates to monthly format can sometimes be wrong if a date happens to be included in a week that is included in a different month. There are ways you can navigate around this issue. For example, you can convert all the dates to a mid-month date and then apply formula Month() or Quarter() or Year() to get the correct time. However, this is not ideal given you have to convert the date to the mid-month every time you want to sum the data and it’s not intuitive.

Therefore, I want to introduce a way to set up the model which fulfills the business requirements to look at the business by week. In summary, instead of setting the Calendar Type in Week format, we can create a fake Week list and then use the fake week list to map all the dates to the fake week list.

Steps to take

  1. Create a fake week list. Fake week list can be set to 13 weeks in total to represent 13 weeks in a quarter. When in use, the time dimension will be the Quarter (in default time scale) + Fake Week List.
    Use Fake Week 1.png
    Use Fake Week 2.png
  2. Create a mapping module to map dates to the week. It can be an import from somewhere else or an input module and you can enter the week cutoff based on how your company manages business. E.g. we use Sunday in each week as the start of the week. There are more days The dimension for the mapping module should be in Day format and the time range should cover all the dates in the data you want to convert.
    Use Fake Week 3.png
    Use Fake Week 4.png
  3. Map date to week.
    Use Fake Week 5.png
    Use Fake Week 6.png
  4. With the data setup above, you have the foundation to build reporting modules. You can sum the source data by Fake Week list and date.
    Use Fake Week 7.png

The fact that we are using the Fake Week list can give a lot more flexibility than setting the calendar to week.

  • This fulfills the business requirements to run the business by weeks. It gives the business flexibility to define the business week as well.
  • At the same time, this keeps the benefits of having standard month calendar in the model. So you could produce reports by regular month and regular quarter as well without the impact of week definition.
  • The reporting module would be in a much cleaner format. It will show in which week we are in the quarter without looking at the specific date.

Questions? Leave a comment!

Tagged:

Comments

  • ashish.banka
    ashish.banka

    @KayK Thanks for putting this together. I think this solution would work if the need have weeks as a stand alone list / dimension rather than a hierarchy to the Month, Quarter and Year.

Categories