Hi All,
Model Calendar is set to Calendar/Months/Years in the model i am working with.
i will not be able to format the line items as time period - Week
Hi @akprasa1 ,
Goto Blue Print view and scroll down to the right and click on Format Column then you will able to see Time Period.
Hi Naushad,
Looks like you did not get what i meant by my model calendar is set to Calendar/Months/Year.
I will not be able to select Week as a option.
Calendar is set to Months/Year
Week selection is disabled
In that case you will not be able to select Weeks and Anaplan too will not be able to show you Weekly Output of a date.
You will have to create a mapping module which can define the relationship between the dates and the weeks and use that mapping module for your reporting purposes.
Hi @akprasa1
Given you model calendar restraints I would recommend using the following approach;
Create a 'Time Properties - Year' module containing only time dimension set to Year. Add a single line item called, 'Start Date.'
Type the following formula - START()
This will populate the line item with the date of the first day of each calendar year.
Create a 'Time Properties - Day' module containing only time dimension but this time set to Day.
Add the following line items;
1. Date, format date, formula START()
1. WeekDay, format number, formula WEEKDAY(Date, 1) The '1' indicates the start of the week, Mon =1, Tue=2 etc
2. NewWeek, format number, formula IF WeekDay = 1 OR NewYear THEN 1 ELSE 0
3. NewYear, format boolean, formula Date = YEARVALUE('Time Properties - Year'.Start Date)
4. WeekNumber, format number, formula CUMULATE(New Week, NewYear)
Set all summary options to none.
The resulting table can be used to LOOKUP the week number for any given date within the model calendar depending on which day of the week each week starts. Adjust the WEEKDAY function to change this.
I have attached some screenshots to help you see what I have suggested.
Good luck,
Chris
Nice one! But two things here
1. Your 53rd week ends with just 2 days.
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)
Would love to hear your thoughts
Hi @akprasa1
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.
The Structure:
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.
Thanks
Anik