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:

Tinker-1

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:

Tinker-2

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.

Advertisements

4 comments on “Tinkering With Week Dates

  1. Jessica says:

    Thanks so much! This is exactly what I needed.

    Liked by 1 person

  2. Kumar says:

    I am looking for the same also, but when I am taking your formula it is showing bad character for day key word and I am also testing on superstore itself.

    Like

    • Kumar. Either you are misspelling the weekday, or you are doing a copy/paste from a Word document. Tableau doesn’t read Word’s quotation marks correctly. Try writing your formula in Notepad or Notepad ++. Let me know if that fixes the problem.

      –Shawn

      Like

      • Simon Clements says:

        Hi Shawn,

        Looks like Tableau doesnt read quotations from Chrome correctly either.
        Needed to adjust to make them work.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s