🧑‍🤝‍🧑 Group Report Rows by Week

By setting your table's Group By to a timestamp export and using Format Type: Custom SQL, you can get an automatic weekly grouping. This article will detail how to group dates by weeks and how to pick which weekday to use for grouping.

Core Concept

Take the difference in weeks from 0 to your date, add that many weeks to 0, and you'll end up with a Monday. Why Monday? In SQL Server, 0 represents 1900-01-01, a Monday. 

Example

cast(dateadd(week, (datediff(day, 0, @val) / 7), 0) as date)

This example report shows the number of applications submitted per week. The screenshot was taken on 2023-12-15, so applications submitted that day are grouped under 12-11:

Details

How does this work? Why not write it like this?

dateadd(week, (datediff(week, 0, @val)), 0)

datediff(week, @startdate, @enddate) works by counting how many times Sunday is crossed. Therefore, an input of Sunday, December 10, 2023 will output Monday, December 11, 2023 because Sunday was crossed. This is probably not what you want.

To solve this, count the number of days, not weeks, and divide by seven. Any remainder will be automatically discarded.

dateadd(week, (datediff(day, '1900-01-01', @val) / 7), '1900-01-01')
dateadd(week, (datediff(day, 0, @val) / 7), 0) --Shorter version

One more step: Wrap the entire thing in cast(... as date) so that the report displays only a date, not a date and time:

cast(dateadd(week, (datediff(day, 0, @val) / 7), 0) as date)

Other Weekdays

What if you want to align everything to Sunday? Use -1 (aka December 31st, 1899) instead of 0 as the base date:

cast(dateadd(week, (datediff(day, -1, @val) / 7), -1) as date)

Similarly, Friday would be 4 (January 5th, 1900).