Calculate Week and Days in a Week: Not a straight forward logic

gaurav_sharma-bng

Hi All,

Hope you are doing great!

I have to create a peculiar way to calculate weeks and then calculate the days in each of these weeks. Please find below the rules to create a week-

1. Weeks will be defined from Sunday to Saturday. The only exception to this rule is stated in rule(4)
2. The usual number of days in a week are 7 days in cases where the Start Date and the End Date of the week lie in the same month
3. The only exception to rule(2) is in case we move from 1 month to the other while calculating a week.
4. From the 1st date of any month, it is mandatory to start a new week irrespective of what day it is. 
5. The total number of days in a week cannot be less than 5 ever. This rule should never be violated

 

Let us look at the example below for January 2019 and September 2019 to better understand the way weeks and days in a week are calculated -

 

	Start Date	End Date	Days in Week
Week 1	01-Sep-2019 (Sun)	07-Sep-2019 (Sat)	5
Week 2	08-Sep-2019 (Sun)	14-Sep-2019 (Sat)	7
Week 3	15-Sep-2019 (Sun)	21-Sep-2019 (Sat)	7
Week 4	22-Sep-2019 (Sun)	30-Sep-2019 (Mon)(End of the old month - if we don't go till 31st, the next week will have less than 5 days)	7
Week 5	01-Oct-2019 (First day of the new month)	05-Oct-2019 (Sat)	5

 

 

	Start Date	End Date	Days in Week
Week 1	01/01/2019 (Tue)	05/01/2019 (Sat)	5
Week 2	06/01/2019 (Sun)	12/01/2019 (Sat)	7
Week 3	13/01/2019 (Sun)	19/01/2019 (Sat)	7
Week 4	20/01/2019 (Sun)	26/01/2019 (Sat)	7
Week 5	27/01/2019 (Sun)	31/01/2019 (End of the old month)	5
Week 6	01/02/2019 (First day of the new month)	09/01/2019 (2nd Sat as if we take the 1st Saturday, the week would have less than 5 days)	9

 

 

 

What I have done -

 

I have created user input to input the start date of this week calculation logic.

I have created a list with members like 1.2.3.4.5.6.7……..52 weeks in them

Start Date of [Week 2] will be the End Date[Week 1]+1

Another line item that calculates Start Date + 7 days

If the months of "Start Date" and "Start Date+7" are different that shows we are month end, so I have added additional logic

But then I get a circular Reference with End Date as my End Date formula again involves Start Date

 

Any support in this regard will be highly appreciated

 

Regards,

GS

LipChean_Soh

Hi @gaurav_sharma-bng 

 

Do see if the following works:

 

1. Create 'Fake Days Details' module with list of 'Fake Days'.

Fake Days is a numbered list. I added 1000 members, which is about 3 years' worth. 

Valid Sunday? is the key line item that should fulfill your functional requirements, i.e. 

1.1 Fall on a Sunday

1.2 More than 5 days from the 1st day of the Month

1.3 More than 5 days from the last day of the Month, so that your final week of the month doesn't have a week less than 5 days, before Week 1 of the new month starts on the 1st day.

 

2. All the formulas should be self explanatory. I'll add the formulas that are not shown in full.

Valid Sunday? = Weekday = 7 AND Day > 5 AND Month Details.Last day of Mth[LOOKUP: Month] - Day >= 4

Last Day of Week = IF Week Num of Mth <> Week Num of Mth[LOOKUP: Next Fake Day] THEN Date ELSE BLANK

 

3. The 'Month & Fake Weeks' module consists of 2 lists, i.e Fake Week, Standard Time (Month)

Fake Week has 5 members, i.e. Week 1, Week 2 ... Week 5, with CODE of 1, 2 .... 5 to represent the different Fake Week

 

4. 'End Date' uses the same FIRSTNONBLANK as 'Start Date'. The difference is in the line item referred, i.e. it refers to 'Fake Days Details.Last Day of Week' instead of Fake Days Details.'Day 1 of Week'

 

5. 'Month Details' is a system module that supported Fake Days Details.Valid Sunday?

 

Thanks,

LipChean

 

anirudh

Hi Gaurav,

 

Having a hard time understanding the 5 rules for the week setup, some of them seem to override each other. For instance, if weeks are defined from Sunday to Saturday, what would you do in case the 1st of the Month is any other day, should the week start from that day on-wards or should we follow rule 1? Also as per Rule 4 if a new week begins, should we ignore Rule 5 even if the previous month's last week is less than 5 days because a new week begins as per rule 4?

 

On the circular reference error, can you please provide a screenshot of the formulas, and I can help further

 

Regards,

Anirudh

gaurav_sharma-bng

Hi Anirudh,

 

Thanks for your reply. I have made changes to the original post to make it more clear. To answer your questions-

a. A new month will always create a new week IRRESPECTIVE of the day on which it starts. This is the only exception to a Sun to Sat week.

b. Let us say 1st of any month was on Friday, then you have 2 days(Fri-Sat) to move to the (Sun to Sat) week. As we cannot have less than 5 days in a week --> We will include the next week as well into the week definition. So the week will be 1st(Friday) - 9th(Sat). The next week will restore from Sunday to Saturday.

 

Please look at the examples of Jan19 and Sep 19 put in the original post. Those share the different scenarios for week.

 

Regards,

GS

gaurav_sharma-bng

Hi @LipChean_Soh ,

 

Thanks a lot for this solution! This seems to be a great way to resolve the problem. 

 

I have actually made substantial progress and have defined all the necessary logic to get correct week values. However, I am stuck at the final issue of circular reference in which I am trying to get the Previous value of "Week End Date" in my "Week Start Date" formula. I wanted to know if there is a way for me to let the system understand that it is not a circular reference since I am referencing to the previous value of the "Week End Date"??

 

 

I have created a list "Fake Weeks" with 52 members in it (1..2..3..4.....52)

 

 

Week List	Week Start Date	Week End Date
1	User Input	Some Calculation using Week Start Date
2	Previous(Week End Date) + 1	Some Calculation using Week Start Date
3	Previous(Week End Date) + 1	Some Calculation using Week Start Date
52	Previous(Week End Date) + 1	Some Calculation using Week Start Date

 

 

Below is a Snapshot of my blueprint - Please look at the text in RED-

 

Weekly Time Dimension
Week Start Date	IF ITEM(Week) = Week.'1' THEN ADMIN Key Controls.CY Week Start ELSE BLANK(I want to Select Previous value of Week End Date)--This gives me circular reference if I use Week End Date[Lookup: Previous Week Property]
6+ Start Date	Week Start Date + 6
No. of days in a month	DAYSINMONTH(YEAR(Week Start Date), MONTH(Week Start Date))
Month End Date	DATE(YEAR(Week Start Date), MONTH(Week Start Date), 'No. of days in a month')
Weekday	WEEKDAY(Week Start Date, 7)
Week End Date	IF Weekday = 1 THEN IF MONTH(Week Start Date) = MONTH('6+ Start Date') THEN IF Month End Date - '6+ Start Date' >= 5 THEN '6+ Start Date' ELSE Month End Date ELSE Month End Date ELSE IF Weekday <> 1 AND 7 - Weekday < 4 THEN Week Start Date + 7 - Weekday + 7 ELSE Week Start Date + 7 - Weekday

 

 

 

Regards,

GS

LipChean_Soh

Hi @gaurav_sharma-bng 

 

This one looks like a circular reference to me, because

'Week Start Date' is referred by 'Week End Date'

AND

you want 'Week End Date' to be referred by 'Week Start Date'.

 

The above would only work if it's applied in a standard time dimension, but your module in this case is using 'Fake Weeks'.

 

Thanks,

LipChean

LipChean_Soh

Hi @gaurav_sharma-bng 

 

I'm curious to hear how you decided to proceed with this issue.

 

Thanks,

LipChean

gaurav_sharma-bng

Hi @LipChean_Soh ,

 

The solution that you provided was of great help in building my own version of the solution. My requirement was slightly different terms, I wanted the weeks to be 1-52(all weeks in a year in a list) rather than (Weeks 1-Week 5) for each month. But, I was able to make tweaks to the logic and get the answer I needed. Thanks again for your suggestions!

 

Also, regarding the Circular reference. If you check the link below, there is a similar solution asked and @nathan_rudman had provided a way to resolve the circular reference by somehow getting the line items mapped to time and using previous to resolve the circular reference. However, I was unable to build it out looking at his description. I would greatly appreciate any inputs on that regards as I have faced similar situations in the past as well.

 

https://community.anaplan.com/t5/Anaplan-Platform/PREVIOUSITEM-PREVIOUS-on-a-list/td-p/24575

 

 

Regards,

GS

nathan_rudman

lemme try to build an example for that. Ill try today