Calculate Week and Days in a Week: Not a straight forward logic
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-
- Weeks will be defined from Sunday to Saturday. The only exception to this rule is stated in rule(4)
- 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
- The only exception to rule(2) is in case we move from 1 month to the other while calculating a week.
- From the 1st date of any month, it is mandatory to start a new week irrespective of what day it is.
- 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 -
Days in Week
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)
01-Oct-2019 (First day of the new month)
Days in Week
31/01/2019 (End of the old month)
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)
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 18.104.22.168.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
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?
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
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.
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
This one looks like a circular reference to me, because
'Week Start Date' is referred by 'Week End Date'
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'.
I'm curious to hear how you decided to proceed with this issue.
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.
lemme try to build an example for that. Ill try today0