Register

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

Certified Master Anaplanner

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

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

8 REPLIES 8
Community Boss

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

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

Certified Master Anaplanner

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

Hi Anirudh,

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

Certified Master Anaplanner

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

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

Certified Master Anaplanner

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

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

Certified Master Anaplanner

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

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

Certified Master Anaplanner

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

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

Thanks,

LipChean

Certified Master Anaplanner

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

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

Master Anaplanner/Community Boss

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

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

Nathan Rudman, Anaplan Model Builder