Tinkering With Week Dates

On the forms this week Emily Archbuckle wanted to aggregate her data by week, but instead of displaying the default beginning-of-the-week date, she wanted to display the end-of-the-week date — and she wanted her weeks to start on Monday, instead of the default Sunday.

After tinkering with this a bit, I discovered that we can combine the DATETRUNC() and DATEADD() functions to give her what she wants. The formula for doing this turns out to be:

DATEADD(‘day’,6, DATETRUNC(‘week’,[Order Date],’Monday’ ) )

So what’s going on here?

In the DATETRUNC() function, we are doing a standard aggregation, telling Tableau to group the data by week. We are also using the optional feature of this function, [start_of_week], to change it from the default ‘Sunday’ to ‘Monday’. All pretty straight forward.

Now for the interesting part. By wrapping this code in a DATEADD() function, we can in effect display any one of the seven days in that week, without changing how the week is aggregated. It just changes the date being displayed in the header. So the formula as written adds 6 days to a week starting on Monday, which displays all the end of week dates on Sundays:

This is a little confusing because Tableau start-of-week dates are normally Sunday. So lets do a different one. Let’s start the week on Wednesday and display the end of week Tuesday:

DATEADD(‘day’,6, DATETRUNC(‘week’,[Order Date],’Wednesday’ ) )

This will produce this chart:

And if for some reason you wanted to display the middle of the week you would just change the 6 to a 3 or 4.

Final Note: Changing the start_of_week value will changes the values in the bar chart because we are aggregating to a different 7-day period. Changing the incremental value (6) in the DATEADD() function will NOT change the aggregation, it only changes the header display.