Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need each week of the year to be within only the month with the most working days.
Example:
On Week 13 of 2021 (29/03/2021 - 02/04/2021) there are 3 working days in march (29/03/2021 - 31/03/2021) and 2 working days in april (01/04/2021 - 02/04/2021).
So I need to set the entire Week 13 as March, including the 2 days of April which should be considered to be March.
This method works but it doen't consider working days. Eventually I found a better solution:
IF(NetWorkDays(WeekStart(Date), MonthEnd(WeekStart(Date))) > NetWorkDays(MonthStart(WeekEnd(Date)), WeekEnd(Date)), Month(WeekStart(Date)), Month(WeekEnd(Date)))) as [Month_Floor]
You can do this identifying the most frequent month using Mode(). See sample script below.
LET _start = '2021-01-01';
LET _end = '2022-04-26';
Calendar:
LOAD WeekName(Date) as YearWeek,
Month(Date) as Month_calendar,
*;
LOAD
dayname(RecNo() -1+ '$(_start)') as Date
AutoGenerate(1+'$(_end)'-'$(_start)')
;
LEFT JOIN
LOAD
YearWeek,
Mode(Month(Date)) as Month
Resident Calendar
Group by YearWeek
;
This method works but it doen't consider working days. Eventually I found a better solution:
IF(NetWorkDays(WeekStart(Date), MonthEnd(WeekStart(Date))) > NetWorkDays(MonthStart(WeekEnd(Date)), WeekEnd(Date)), Month(WeekStart(Date)), Month(WeekEnd(Date)))) as [Month_Floor]