When you tear out pages from the calendar, the standard Gregorian calendar seems to be natural. When planning and comparing the periods in trade and production, often other solutions are more helpful. What can Tableau offer in this respect? ISO-Dates that comply with the ISO 8601 standard!
When to use calendars according to ISO 8601
Imagine a company that produces machine components. In this business, planning the calendar of deliveries, both to own warehouses and end customers is vital. In planning, you need to consider such aspects as stocks of components in specific weeks of previous years. In order to compare weeks, they must have the same length. This often raises the problem of the first and the last day of the year. To resolve this issue, a special calendar was invented, which dates are called ISO week date. Its primary assumption is that each week always has 7 days. Where does it work best? For governments, to calculate the financial years. However, it plays a crucial role in industrial and commercial matters. This is the reason why it was called Industrial Date Coding before it was standardized by the International Standard Organization (ISO).
Calendar with ISO week date – How does it work?
According to ISO 8601, each week in a year starts on Monday and lasts 7 days, including the first and the last days of the year. A year can last 364 or 371 days, which is 52 or 53 weeks. The first week of the new year must have at least 4 days from the new year of the Gregorian calendar. This means that the numbering of weeks may not be consistent with the numbering of the Gregorian calendar. Also, the years may differ in the last days of December and the first days of January.
How to use ISO dates in Tableau?
In order to use this date format in Tableau, you can choose one of the two available methods. This all depends on whether you will be using ISO dates only, or combining them with the dates from the Gregorian calendar. In the first case, you just need to go into the Date Properties of the data source selected and swich the Standard Gregorian calendar to ISO-8601 Week-Based. This will result in having all-time units in the desired format. On the shelves, you will not see the YEAR([Order Date]), but ISOYEAR([Order Date]).
The Date Properties are in the top text menu, in the Tab: Date/Name of data source/Date Properties.
When analysing the dates in the format consistent with ISO-8601, you will be able to use fewer date units than in the standard calendar. You can choose the following ISO: -year, -quarter, -week and -day of the week.
In the second scenario, you mix different date formats. You can create the ISO dates by using the following functions: ISOYEAR(), ISOQUARTER(), ISOWEEK() and ISOWEEKDAY(). If you don’t modify the Date Properties, the ISO dates will coexist with other time functions (for example, YEAR()). In order to use the DATETRUNC() or DATEPART() functions, in addition to the standard time formats, you can use the following: “iso-year”, “iso-quarter”, “iso-week” and “iso-weekday”.
ISO-8601 and 4-4-5 calendar
Another version of the calendar used in the industrial and commercial matters is the 4-4-5 calendar. It divides the year into 4 quarters of 13 weeks. Each quarter consists of two 4-week months and one 5-week month. The longer month can be the first, the second or the third month, depending on the corporate policy. Its main advantage is that every period has the same length and ends on the same day of the week. This facilitates the planning of the production or shifts. On the other hand, longer months disrupt comparing month to month. As a rule, one year has 52 weeks and 364 days, which means that an additional week is added every 6 years.
It is easy to see that this system has a lot in common with the ISO-8601 calendar. The number of days and weeks in a year is the same. The length of quarters will usually be identical, except for the 53rd year, where they can be arranged differently. For this reason, you need to calculate the number of the quarter, and to determine the number of the segment (“month”) in the quarter. Both calculations are presented below.
Lastly, one important note for those of you who worked with ISO-dates in the past. At that time, when you chose the Workbook Locale for some countries, the ISO dates were unavailable. Fortunately, this problem has already been addressed.
Agata Mężyńska, Tableau Desktop Certified Professional
