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:

  1. Right-click (control-click on Mac) the data source in the Data pane to open the Date Properties dialog box.
  2. 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 January 1. This means the year may start anywhere between December 29 and January 4. Gregorian calendars always start the year on January 1. 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, through 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 visualization

  1. Open Tableau Desktop and connect to the Sample-Superstore saved data source.
  2. 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.
  3. From the Data pane, drag Order Date to the Rows shelf.
  4. On the Rows shelf, click the '+' icon on ISOYEAR(Order Date) to expand out ISOQUARTER(Order Date).
  5. 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 (analogous to months) with 4 or 5 weeks in them. We must find how many weeks are in each quarter to divide them into segments.

  1. Select Analysis > Create Calculated Field.
  2. Name the calculated field Week of Quarter
  3. 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

  1. Select Analysis > Create Calculated Field.
  2. Name the calculated field 4-4-5 Segment.
  3. 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.

  4. 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) and 9 (for the next five weeks, 5–9)
  • 5-4-4 cutoffs: 5 and 9

See Also

Dates and Times(Link opens in a new window)

Date Functions(Link opens in a new window)

 

Thanks for your feedback!