2. When the model set up is at months, wouldn't it make much more sense to map the modules manually (one time set up) instead of letting Anaplan do the work for fake timescale . (There has to be a fake time scale somewhere if anything needs to be reported at week level)
Re: how to calculate WEEKNUMBER of a particular date in a year
My first thought was to create a manual mapping as you suggest but this would require ongoing maintenance which I try and avoid.
This approach will create two part weeks ( week 1 and week 53 ). If the user wishes to create mapping with complete weeks then we need to fix the weekday number to equal that of the first day of the year.
We could achieve this by adding an extra line item in the Time Properties - Year module where we state what WeekDay number the first day of each year is.
If we amend the WeekDay and NewWeek formula above to reference this number rather than using a hard coded reference then the WeekNumber count will start from the first day of the year and increase by one every 7 days.
As you cant embed YEARVALUE into another function create a new line item in the Time Properties - Day module and use the following formula to reference the firs day of each new week.
YEARVALUE('Time Properties - Year'.DayNumber of new week)
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA
I have seen couple of solutions posted on this thread. I gave this a try, let me know if this is useful.
Things to consider:
Because the timescale in your model is at a monthly level, you can't specify when the week starts - on a Sunday/ Monday etc. In this case, I have assumed that the week starts on a Sunday. This can be changed based on your need.
You need 2 modules to identify the week number - one is a setup/ settings module, the other one is the core calc module.
Module - Year Start Calc:
Purpose of the module is to identify the start of the year based on the timescale. Current model timescale is from 1 Jan 2020 to 31 Dec 2020 at a monthly level. Now, 1 Jan 2020 is a Wednesday. Idea is to find the start of the week using the number of days in a week. Below is the structure and calc of this module:
You can club the 3 formulas in one if you wish to. I broke it down to show the flow of calc.
Module - Week Number Calculation:
Purpose of this module is to calculate the week number based on a date input. The first line item is for the input and rest 3 line items calculate the week number as desired.
I have tested with the dates in year 2020 and found it to be matching - let me know if you see any discrepancies.
This should scale if you increase the timescale to more years as it uses a lookup of year rather than a static input. Let me know if this helps.