Week in, week out, Tableau

Week numbers with Tableau

© Claudia Hautumm / www.pixelio.de The previous post discussed different methods of calculating week numbers and their effects on analysis and visualizations of data on a weekly basis. The post was focused on using Microsoft Excel.

However, the challenge caused by different week numbering methods is not restricted to Microsoft Excel. I guess every data analysis and visualization software faces this problem.

And so does my favorite: Tableau. There is no built-in way to switch between different week numbering methods in Tableau. But Tableau has the flexibility for an easy-to-use solution. This post provides all information of how to use ISO 8601 week numbers in Tableau, including a Tableau packaged workbook for free download.

The background: Tableau’s standard: US week numbering

Tableau’s default is the US week numbering, i.e. weeks start on Sunday and week 1 of a year is the period from January, 1 until the following Saturday. Thus, the first week of a year can have anything between 1 and 7 days.

Connecting to the example data already used in the previous post, Tableau produces by default the following bar chart:

Tableau Default - click to enlarge

The sum of the values for the week from Sunday December 27, 2009 to Saturday January 2, 2010 is split between week 53 of 2009 and week 1 of 2010. As already discussed in the previous post, I consider this an undesirable effect: from my point of view, the inhomogeneous lengths of the periods on the x-axis are misleading.

How to avoid this effect

1. The obvious idea, but not the solution

The first idea that probably comes into everybody’s mind is adding a data field containing the week number to the original database. Doing this (using the ISO 8601 week numbering method) and dragging this field to Tableau’s column shelf produces the following result:  

Week Number in Source Data - click to enlarge

The affected week (no. 53) is now the week from Monday, December 28, 2009 to Sunday, January 3, 2010 (ISO 8601 weeks start on Monday).

But again this week is split into two bars since YEAR(Date) is used on the column shelf as well. Thus Tableau separates week 53 by year: Week 53 of 2010 is not the last week of 2010. It represents the sum of the values (530) from January 1 to January 3, 2010. These 3 days belong to week 53 of 2009 in the additional week field in the database, but they are displayed in week 53 of 2010.

Conclusion: this approach doesn’t solve our problem. In fact it is even worse and more confusing than Tableau’s default.

2. One step up: a calculated field

A viable step forward is using a calculated field in the workbook, called ‘Monday of Week’. The calculation formula is

DATETRUNC(‘week’,[Date])

The formula converts all dates in the database into the previous Monday. Putting YEAR(Monday of Week) and MDY(Monday of Week) to the column shelf creates this visualization:

Calculated Field Monday - click to enlarge

In principle this is what we are looking for.

But there is still a drawback: the labels of the x-axis are showing the dates (Mondays) instead of week numbers. If you don’t mind displaying the Mondays, this is an easy way to replace Tableau’s default week numbering by ISO 8601 weeks. But what if your boss or client insists on the week numbers?

3. The best of 2 worlds? Combining the additional data and the calculated field

Combining the first two approaches (week number in the original data and the calculated field “Monday of Week”), i.e. having YEAR(Monday of Week) and calendar week on the column shelf, produces this chart:

Week Number in Source Data and Calculated Field - click to enlarge

Well, that’s it, right?

No, not yet.

Requiring an additional field in the original source data is quite a workaround. It might be ok if you are using a Microsoft Excel workbook or a Microsoft Access database as your source data, provided you have full control over these files. But what if you are connecting to a larger SQL database and /or you do not have access to the data structure, i.e. you are not able to add another field?

4. The solution

Of course there is a better way:

We need another calculated field, this time calculating the week number according to the ISO 8601 standard. This is the formula of the new calculated field called ISO 8601 week:

ROUND((DATEADD(‘day’, 3, DATETRUNC(‘week’,[Date]))-
DATEADD(‘day’, 3, DATETRUNC(‘year’, DATEADD(‘day’, 3, DATETRUNC(‘week’,[Date])))) +
DATEPART(‘weekday’, DATEADD(‘day’, 2, DATETRUNC(‘year’, DATETRUNC(‘week’,[Date]))))
-4) / 7 + 1,0)

Dragging YEAR(Monday of Week) and ISO 8601 week to the column shelf finally creates the visualization we are looking for:

ISO 8601 Calculated Field - click to enlarge

Download the Tableau Workbook

Download the Tableau Packaged Workbook with the examples of this post for free:

Download Week Numbers with Tableau (Tableau Packaged Workbook, 57.8K)

To open this workbook you need Tableau 4.1 (14-day free trial) or the free Tableau Reader.

Last, but not least

Many thanks for the idea, Giedre.

Update on September 1, 2009 – Changes in version 5.0 of Tableau 

The workbook and the screenshots in this post have been produced with Tableau Desktop version 4.1.5. I just noticed that – with the release of Tableau version 5.0 – the DATETRUNC function changed: DATETRUNC(‘week’,[Date]) returns the Sunday of the week of [Date] rather than the Monday as it did in previous versions.

Thus, you have to change the formulas of the calculated fields Monday of Week and ISO 8601 week: Replace DATETRUNC(‘week’,[Date]) by DATETRUNC(‘week’,[Date]-1)+1 and your Tableau 5.0 workbook will produce the same results as shown in the screenshots of the article.

For your convenience, here is a link to a packaged workbook for version 5.0 for free download:

Download Week Numbers with Tableau version 5.0 (Tableau Packaged Workbook, 32.8K) 

Add a Comment

Your email address will not be published. Required fields are marked *