In Sigma, it’s possible to use DateTrunc() to find the week of a given date for weeks starting with Sunday or for weeks starting with Monday using the precision argument.
But it’s also common to want to define weeks as starting by some other day of the week, according to your business needs.
You can find the week of a date starting with any given day of the week using a few simple formulas below:
[Day of Date] = DateTrunc("day", [Date])
[Weekday of Date] = Weekday([Date])
[WeekStart] = The weekday that you want your week to start with, represented as a number from 1 to 7 (with 1 being Sunday and 7 being Saturday)
[Week of Date] =
If(
[Weekday of Date] < [WeekStart], DateAdd("day", [WeekStart] - [Weekday of Date] - 7, [Day of Date]),
[Weekday of Date] > [WeekStart], DateAdd("day", [WeekStart] - [Weekday of Date], [Day of Date]),
[Day of Date])
Or, if you would prefer to copy/paste a single formula that only relies on [Date] and [WeekStart], the formulas above can be condensed into a single formula, given below:
If(
Weekday([Date]) < [WeekStart], DateAdd("day", [WeekStart] - Weekday([Date]) - 7, DateTrunc("day", [Date])),
Weekday([Date]) > [WeekStart], DateAdd("day", [WeekStart] - Weekday([Date]), DateTrunc("day", [Date])),
DateTrunc("day", [Date]))
You can even allow users to decide which day of the week they want their weeks to start with using a control.
You can do so by using the same formulas above, but instead of using a hardcoded value for WeekStart, you can use a List Value control with a manual list of numbers 1-7 with display values of the days of the week:
Be sure to set each of the settings as shown above!
Hopefully this has been helpful!
Please let us know in the comments if you have any thoughts or questions on this!
-Chandler Phelps, Sigma Support