ISO-8601 Week-Based Calendar
The ISO-8601 Week-Based Calendar is an international standard for date-related data. The purpose of the ISO-8601 calendar is to provide a consistent and clear method to represent and calculate dates. ISO-8601 calendars divide dates into years, quarters, weeks and weekdays. Unlike the Gregorian calendar, ISO-8601 calendars have a consistent number of weeks in each quarter and a consistent number of days each week. This makes the ISO-8601 calendar popular when calculating retail and financial dates.
Set ISO-8601 as the default calendar
To set the ISO 8601 calendar for a data source, follow these steps:
- Right-click (control-click on Mac) the data source in the Data pane to open the Date Properties dialog box.
- Set the Default calendar field to ISO-8601 Week-based.
For each date field on the shelf, you can set the calendar type separately. In the shelf, right-click a date dimension (Ctrl-click on Mac), and select ISO-8601 Week-Based.
Differences between ISO-8601 and Standard Gregorian calendars
Not all date levels are relevant in a fiscal calendar.
| Gregorian | ISO-8601 | Differences between calendars |
|---|---|---|
| Year | ISOYEAR | ISO-8601 years always start on the first Monday closest to 1 January. This means the year may start anywhere between 29 December and 4 January. Gregorian calendars always start the year on 1 January. This can cause some discrepancy in years between the two calendar systems around those dates. Each ISO-8601 year is either a long or a short year, with 52 or 53 weeks, depending on when the ISO-8601 year begins. |
| Quarter | ISOQUARTER | The first three quarters in the ISO-8601 always have 13 weeks. The last quarter has either 13 weeks (in a short year) or 14 weeks (in a long year). |
| Month | The ISO-8601 week-based calendar doesn’t use months. Many retail and financial systems divide ISO-8601 quarters into three segments of 4-4-5 weeks, though other "month" patterns also exist. These can be defined using calculations. See Create a 4-4-5 calendar. | |
| Week Number | ISOWEEK | All weeks in the ISO-8601 week-based calendar have exactly 7 days and start on a Monday. Each week belongs to single year, unlike the Gregorian calendar which permits weeks to extend across years. Long years have 53 weeks, short years have 52 weeks. |
| Day | The ISO-8601 week-based calendar doesn’t use numerical days (such as the 14th or 23rd). | |
| Weekday | ISOWEEKDAY | ISO-8601 weekdays always start on Monday and are represented as single digits. Monday is 1, Tuesday is 2, up to 7 for Sunday. |
Date Functions that support ISO-8601
The following date functions are compatible with the ISO-8601 week-based calendar.
DATEADD and DATEDIFF
The result truncates an ISO-8601 date to the nearest ISO level if non-ISO-8601 date parts (day or month) are used. For example, adding one month to an ISO-8601 date results in the iso-week changing.
DATENAME
The result is a Gregorian date unless you use the iso-year, iso-quarter, iso-week, or iso-weekday date parts.
DATEPART
The result is a Gregorian date unless you use the iso-year,iso-quarter, iso-week, or iso-weekday date parts.
DATETRUNC
Truncates an ISO-8601 date to the nearest ISO level. For example, even though the ISO-8601 doesn’t use months, truncating the ISO date at 'month' displays the first iso-week of the Gregorian month.
Create a 4-4-5 calendar
The ISO-8601 week-based calendar doesn’t use months. Many retail and financial systems divide ISO-8601 quarters into three segments of 4-4-5 weeks, though other segment systems also exist.
Follow along with these steps to learn how to implement a 4-4-5 calendar in Tableau Desktop.
Step 1: Set up the visualisation
- Open Tableau Desktop and connect to the Sample-Superstore saved data source.
- Right-click the data source in the Data pane to open the Date Properties dialog box. Change the default calendar to ISO-8601 Week-based.
- From the Data pane, drag Order Date to the Rows shelf.
- On the Rows shelf, click the '+' icon on ISOYEAR(Order Date) to expand out ISOQUARTER(Order Date).
- From the Data pane, drag Sales to the Columns shelf.
Step 2: Find the number of weeks in each quarter
In a 4-4-5 segment calendar, each quarter is divided into 3 segments (c to months) with 4 or 5 weeks in them. We must find how many weeks are in each quarter to divide them into segments.
- Select Analysis > Create Calculated Field.
- Name the calculated field Week of Quarter
- Enter the following calculation in the calculation dialog
DATEPART('iso-week', [Order Date]) - (DATEPART('iso-quarter', [Order Date])-1)*13
Step 3: Create the 4-4-5 calendar calculation
- Select Analysis > Create Calculated Field.
- Name the calculated field 4-4-5 Segment.
- Enter the following calculation in the calculation dialog
IF([Week of Quarter] <= 4) THEN "1" ELSEIF ([Week of Quarter] <= 8) THEN "2" ELSE "3" END
The values 1, 2, 3 are labels for the three segments and can be replaced with any value you choose.
- From the data pane, drag the 4-4-5 Segment calculated field to the Rows shelf.
The resulting viz now has each quarter broken down into three segments.
Modify the pattern
In the 4-4-5 Segment calculation, the cutoffs <=4 and <=8 come from the 4-4-5 pattern.
The first segment's cutoff is <=4 for the first four weeks (1-4). The second segment’s cutoff is <=8 (encompassing weeks 5–8), and the remaining weeks (9-13) are captured in the ELSE portion. (IF statements are calculated until a condition is true, so the second segment only needs to specify the upper limit.)
These cutoffs can be modified for other patterns. Specifically:
- 4-5-4 cutoffs:
4(for the first four weeks, 1–4) and9(for the next five weeks, 5–9) - 5-4-4 cutoffs:
5and9
See Also
Dates and Times(Link opens in a new window)
Date Functions(Link opens in a new window)