Spotfire® Tips & Tricks: Reusable, Simple & Easy ISO Week Calculation with Spotfire

This article presents Spotfire® Tips & Tricks for a reusable, simple, and easy ISO Week calculation using Spotfire.

Introduction

ISO week date is defined in Wikipedia as a leap week calendar system. A date is specified by the ISO week-numbering year in the format YYYY, a week number in the format ww prefixed by the letter 'W', and the weekday number, a digit d from 1 through 7, beginning with Monday and ending with Sunday.

Background

ISO 8601 Data elements and interchange formats ? It is an international standard for representation of dates and times. It was issued by the International Organization for Standardization (ISO) and was first published in 1988. The most important purpose of this standard is to provide an unambiguous and well-defined method of representing dates and times, which will avoid misinterpretation of numeric representations of dates and times, particularly when data transfer happens between countries with different conventions for writing numeric dates and times.

ISO Week with a Custom Expression

The easiest way to compute ISO week numbers from data is to use a calculated column using the following custom expression written by Mathieu Guillot:

If(Floor((10 + DayOfYear([Date]) - Integer(If(DayOfWeek([Date])=0,7,DayOfWeek([Date])))) / 7) in (0, 53),
CASE
WHEN (Month([Date])=1) AND (DayOfWeek(Date(Year([Date]),1,1))<5) AND (DayOfWeek(Date(Year([Date]),1,1))>=1) THEN 1
WHEN (Month([Date])=1) AND (DayOfWeek(Date(Year([Date]),1,1))=5) AND (DayOfYear([Date])<4) THEN 53
WHEN (Month([Date])=1) AND (DayOfWeek(Date(Year([Date]),1,1))=0) AND (DayOfYear([Date])=1) THEN 52
WHEN (Month([Date])=1) AND (DayOfWeek(Date(Year([Date]),1,1))=6) AND (DayOfYear(Date(Year([Date]) - 1,12,31))=366) AND (DayOfYear([Date])<3) THEN 53
WHEN (Month([Date])=1) AND (DayOfWeek(Date(Year([Date]),1,1))=6) AND (DayOfYear(Date(Year([Date]) - 1,12,31))=365) AND (DayOfYear([Date])<3) THEN 52
WHEN (Month([Date])=12) and (Day([Date])=31) AND (DayOfWeek(Date(Year([Date]),12,31))=1) AND (DayOfYear(Date(Year([Date]),12,31))=366) THEN 1
WHEN (Month([Date])=12) AND (DayOfWeek(Date(Year([Date]),12,31))=1) AND (DayOfYear(Date(Year([Date]),12,31))=366) THEN 52
WHEN (Month([Date])=12) AND (DayOfWeek(Date(Year([Date]),12,31))>=1) AND (DayOfWeek(Date(Year([Date]),12,31))<4) THEN 1
WHEN (Month([Date])=12) AND (DayOfWeek(Date(Year([Date]),12,31))=4) THEN 53
WHEN (Month([Date])=12) AND ((DayOfWeek(Date(Year([Date]),12,31))=0) OR (DayOfWeek(Date(Year([Date]),12,31))=6)) THEN 52
WHEN (Month([Date])=12) AND (DayOfWeek(Date(Year([Date]),12,31))=5) AND (DayOfYear(Date(Year([Date]),12,31))=366) THEN 53
WHEN (Month([Date])=12) AND (DayOfWeek(Date(Year([Date]),12,31))=5) AND (DayOfYear(Date(Year([Date]),12,31))=365) THEN 52
END,
Floor((10 + DayOfYear([Date]) - Integer(If(DayOfWeek([Date])=0,7,DayOfWeek([Date])))) / 7))

Open Spotfire and load data with a Date or DateTime column
Go to Data > Add calculated column...

Copy and paste the custom expression and make sure to replace "Date" with the name of your Date or DateTime column
Name the calculated column to add

You are done. A new column with ISO week number is added to your data table.

ISO Week with a Data Function

It is also possible to calculate ISO week in Spotfire using R data functions. Here are few simple and easy ways to perform this calculation.

Since this standard has been out since 1988 there are lots of preexisting solutions for this calculation. One of the CRAN packages that can easily help is ISOWeek. For detailed documentation about the package download from resources.

Install the package ISOweek using TERR tools.

Go to Tools > TERR Tools
Open Package Management tab
Click Load
Search for "ISOWeek"
Install ISOWeek package and close

Once the package is installed you can either use a data function or an expressions function to perform the calculation. In this example, the ISOweek function defined in the package is used to calculate ISO week.

Calculate with an Expression Function

Go to Data > Data functions properties
Open Expression Functions tab
Click New
Name the expression function to add
Copy and paste the following expression and make sure to replace "Date" with the name of your Date or DateTime column
```
library(ISOweek)
output<-ISOweek(Date)
```
You are done. Once you added the expression function, ISOweek would be one of the available functions. Please note that it is a column function and returns a string type.

Calculate with a Data Function

You can also create a data function, which can add a new column to the existing data table. Here is a sample data function that appends a column to existing data in Spotfire.