Many companies just love weeks and request reports being done on a weekly or by-week basis. That is all fine, fun and games but there are a couple of things you need to be aware of, especially if you are either just starting with Tableau, or week-based reporting or if your company is located in different parts of the world.
Week numbers – know the differences
European week counting is based on ISO 8601 which defines the first week of the year to be the one in which the first Thursday falls. This means that 1 January or even 2, 3 January can be week wise in week 53 of the previous year, as was the case in 2021.
A week starts on Monday, therefore a week is always full 7 days and the year has 52 or 53 weeks.
Contrasting, the standard Gregorian Calendar starts week 1 always on the first of January. In extreme cases, this can lead to week 1 only having 1 day. This becomes especially important when running comparisons on a weekly basis as Week 1 can be completely meaningless.
Furthermore, differences can be seen as regards starting day of the week. As stated, ISO considers Monday to be day 1 whereas Gregorian traditionally starts on Sunday; and in some countries like Dubai, the week starts on Saturday. All this must be kept in mind when discussing weeks.
To further potential confusion, America usually starts the week on Sunday, but the workweek starts on Monday. Then again, week numbering is rather uncommon in the United States, instead they say “week of [date] “ ← now its up to you to find out if this refers to Sunday or Monday…
Pitfalls in Tableau
Data Source Settings
For the above reasons its very important to check your data source settings. Unless the data source comes with attributed date settings, Tableau will use your locale settings.
You can check the settings by clicking on “DATA → Go to your Data Source → Date Properties”
Formulas and calculations
Tableau offers a range of different options to derive weeks:
1 week( [DATE] )
2 datepart('week', [DATE], day of week start (optional) )
3 isoweek([DATE])It is very important to know what you are selecting
- week formula: always gives the Gregorian calendar week, regardless of other settings.
- datepart formula: be precise in your settings. When you leave out the day of week start it Tableau will use the first day as defined in your date properties for the data source. Note that there is also the option to use datepart(‘iso-week’, [DATE]) Note: in this case, you cannot set the start day for the week (even though the tooltip says so but Tableau will throw an error if you try to start iso week on a Sunday)
- isoweek( [DATE] ) : not available on all connections; for example, when directly connected to bigQuery, this is not availalbe (like several other date functions). For published data sources, this is available
Here is an example for different week results using different formulas. Notice the data source is set to Gregorian calendar, starting on Monday:
As you can see, isoweek and datepart isoweek return the same values. But they differ 1 week from datepart week when the same is starting on Monday (i.e. this is the same as for the ISO) and even two weeks when the datepart calculation is set to deviate from the data source date properties setting by artifically using Sunday as the week start day.
Finally, the pure “week” formula always gives the Gregorian Sunday values (Important: this is nowhere specifically stated by Tableau but as you can see above, it is this way).
Once again: you have to keep this in mind. Best thing is to use ISO WEEK as this is what the E&V org is used to due to historical reporting.
Errors can also occur when using datediff functions on a week scheme like
datediff('week', [DATE 1], [DATE 2])Depending on your settings, deviations can change
DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'monday')= 1
DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'sunday')= 0The first expression returns 1 because when start_of_week is ‘monday’, then 22 September (a Sunday) and 24 September (a Tuesday) are in different weeks. The second expression returns 0 because when start_of_week is ‘sunday’ then 22 September (a Sunday) and 24 September (a Tuesday) are in the same week.
And that’s it for today.
I hope you found that useful. As always appreciate your feedback,
Until next time.
Steffen
Tableau Adventurer 