I am creating a pivot table that groups counts of service by week. The service week goes from Friday to Friday. Example Oct 15 to 22, 2021 is a service week and October 22 to 29, 2021 is a different service week. I don't know why they do it this way, but as you can see the 22nd falls into both weeks.
I wrote a code that indicates what week of the year coordinates w the service grouping.
=WeekName((weekstart([Service Date], 0, 4)), 1)
Oct 15 - Oct 21
2021/41
Oct 22 - Oct 28
2021/42
My issue is that is accounting for a week going from Friday to Thursday, which is throwing off my counts. I need the table to look like:
Oct 15- Oct 22
2021/41
Oct 22 - Oct 29
2021/42
I understand that means some of the counts will overlap but that's fine. Does anyone have a recommendation on how to create this grouping?
It sounds like having a master calendar to map each [Service Date] to [Service Week] would be useful here. The calendar table would have two rows for every [Service Date] that is a Friday, and one row for other days. You could generate it in the load script with something like this:
// Create Friday to Thursday Master Calendar CalendarMaster: LOAD Date([Service Date]) AS [Service Date], WeekName([Service Date],0, 4) AS [Service Week], ; Load Date(MinDate + IterNo() -1 ) AS [Service Date] While (MinDate + IterNo() - 1) <= Num(MaxDate); Load date('2021/01/01') AS MinDate, date('2021/11/01') AS MaxDate autogenerate 1;
//Add extra Friday only Concatenate Load [Service Date], [Service Week]-7 as [Service Week] resident CalendarMaster where WeekDay([Service Date]) = 'Fri';
