US Public Holidays in Tableau

Calculated Fields in Tableau for US Public Holidays

For the analysis of daily data, the weekday can be an important and helpful additional information.

For instance: if you are analyzing sales or order figures, incoming calls in a call center, internet usage, web statistics or the like, you will probably see significant differences in the data between working days, weekends and public holidays.

Your views and dashboards should enable the reader to immediately distinguish between the three types, e.g. by color coding. This is pretty easy to do for the weekends, but it takes some additional efforts to include the public holidays, too.

Today’s post explains why color coding daily data by the type of the day can be important for analysis and visualization. The article provides and describes Calculated Fields in Tableau for all US public holidays and put them to use in a Tableau Public dashboard showing an interactive calendar with all public holidays in the US between 1900 and 2099.

Is it worth the effort?

Why should you highlight public holidays in your views and dashboards?

Let’s have a look at an example. Here is an excerpt of some web analytics data of this blog for one month:

You can easily see significant differences. The pattern (2 small values, followed by 5 larger numbers, followed by 2 small numbers again) suggests that this is a workdays/ weekend slope. But you do not know for sure and you do not see it at a glance.

Let’s try to improve the view the easy way. We add the weekdays of the dates to the view:

Better than before and it confirms our workdays / weekend assumption. However, your eyes have to jump back and forth between the chart area, the labels and the axis to identify the weekends.

So, let’s make this easier for the reader by color coding the weekends:

Now that’s much better and we can see the weekends at a glance.

However, have a look at the last four columns. Only 29th and 30th are a weekend. Why are the numbers so small on a Thursday and Friday? You guessed it: public holidays. Let’s add them to the color coding of the view:

Now you see it: we are looking at November and the fourth Thursday in November (the 27th in this case) is Thanksgiving. I do not have an explanation for the small number on the day after Thanksgiving, but my wild guess would be that many people took the day off for a long weekend.

There is another interesting fact visible at a glance with this color coding: the value on the other public holiday in November (11th: Veterans Day) is comparable to a working day. This means that the numbers are significantly smaller on weekends and public holidays, but not on all public holidays.

In a nutshell: color coding weekends and public holidays can provide a lot of additional interesting information at a glance.

I see two options to realize public holidays in Tableau: Data Blending and Calculated Fields.

Option 1: Public Holidays by Data Blending

This is certainly the easier way. First, you create a list of all holidays (e.g. in Microsoft Excel), maybe looking like this:

To save you some time, here is such a list for free download covering the holidays from 1900 to 2099:

Download US Holidays (zipped Microsoft Excel 2007-2013 workbook, 785.1K)

Next, you use Tableau’s data blending feature to add this list as a new data source of your workbook and define the relationship (if Tableau didn’t automatically):

That’s it. A little data preprocessing to get the list of public holidays, a few mouse clicks in Tableau and you are good to go.

Tableau Calculated Fields for US Public Holidays

The other, more complicated option is to create a Calculated Field for each holiday.

Public holidays can be divided into two different categories: fixed and variable.

Fixed holidays always fall on the same day of the same month, like New Year’s Day or Independence Day. In the US, the variable holidays are all defined by a “nth weekday of a defined month” rule. e.g. Martin Luther King Jr. Day is the third Monday of January and Memorial Day is the last Monday of May.

Fixed Public Holidays

Let’s start with the easier ones: the fixed holidays.

Here are the Calculated Fields in Tableau for the fixed public holidays:

New Years Day (January, 1):

DATETIME(STR(YEAR([Date]))+ “-1-1″)

Independence Day (July, 4):

DATETIME(STR(YEAR([Date]))+”-7-4″)

Veterans Day (November, 11):

DATETIME(STR(YEAR([Date]))+”-11-11″)

Christmas Day (December, 25):

DATETIME(STR(YEAR([Date]))+”-12-25″)

How does this work?

YEAR([Date]) returns the year of [Date]. STR converts the year to a string. The fixed month and day are then concatenated at the end of this string with a hyphen out in front. Finally DATETIME converts the string back to a date.

Variable Public Holidays

To implement the variable holidays, you first have to understand how to work with weekdays in Tableau.

The function DATEPART (date_part, date, [start_of_week]) returns a part of the given date as an integer. DATEPART(‘weekday’, [Date]) returns a number indicating the day of the week: 1 for Sunday, 2 for Monday, …, 7 for Saturday.

To implement the “nth weekday of a defined month” rules, the Calculated Fields start at a certain day in the month (e.g. two weeks after the first day of the month) and then add days to it based on the weekday of this starting date and the defined weekday of the holiday.

Too academic? Ok, let’s have a closer look at the first variable public holiday in the US:

Martin Luther King Jr. Day: 3rd Monday of January

DATETIME(STR(YEAR([Date]))+”-1-15″) +

IF DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-1-15″)) > 2 THEN

9 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-1-15″))

ELSE

2 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-1-15″))

END

Starting point of the calculation is the 15th of January, i.e. two weeks after New Year’s Day (first line). The following IF clause checks if the weekday of January 15 is Tuesday to Saturday (i.e. weekday > 2). If so, it adds 9 and subtracts the weekday of January 15. Else (January 15 is a Sunday or Monday), it adds 2 and subtracts the weekday.

Let’s look at the results of the inner calculations for two examples:

Year 2015:

January 15, 2015 was a Thursday, i.e. its weekday is 5. The formula calculates as follows: it starts at the 15th of January. Since the IF condition is true (5>2), it adds 4 days  (=9-5) to this date. The result: January 19th 2015, the third Monday of January 2015.

Year 2012:

January 15, 2012 was a Sunday, i.e. its weekday is 1. Again, the formula starts at January 15. The condition is false (1<2) and in the ELSE part of the IF clause, 1 (=2-1) day is added to the start date. The result: January 16th 2012, the third Monday of January 2012.

This is the basic logic in all Calculated Fields for variable public holidays in the US. Here are the other formulas:

Presidents’ Day: 3rd Monday of February

DATETIME(STR(YEAR([Date]))+”-2-15″) +

IF DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-2-15″)) > 2 THEN

9 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-2-15″))

ELSE

2 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-2-15″))

END

Almost the same formula as for Martin Luther King Jr. Day, just replacing January by February.

Memorial Day: Last Monday of May

DATETIME(STR(YEAR([Date]))+”-6-1″) +

IF DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-6-1″)) > 2 THEN

9 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-6-1″))

ELSE

2 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-6-1″))

END

– 7

This Calculated Field starts at the first day of the next month (June) and finally subtracts one week (7 days) after the IF clause.

Labor Day: 1st Monday of September

DATETIME(STR(YEAR([Date]))+”-9-1″)+

IF DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-9-1″)) > 2 THEN

9 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-9-1″))

ELSE

2 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-9-1″))

END

Columbus Day: 2nd Monday of October

DATETIME(STR(YEAR([Date]))+”-10-8″) +

IF DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-10-8″)) > 2 THEN

9 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-10-8″))

ELSE

2 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-10-8″))

END

Thanksgiving: 4th Thursday of November

DATETIME(STR(YEAR([Date]))+”-11-22″) +

IF DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-11-22″)) > 5 THEN

12 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-11-22″))

ELSE

5 – DATEPART(‘weekday’, DATE(STR(YEAR([Date]))+”-11-22″))

END

Enhancement: Shift Public Holidays to the closest workday

I learned from Wikipedia that public holidays falling on a Saturday or Sunday are observed by federal employees on the closest working day. Since all variable holidays cannot fall on a weekend by definition, this affects only the fixed holidays. I am not sure if this is really necessary, but you can easily add a “shift to closest workday” option to your calculations:

Let’s say [Shift to closest workday] is a Boolean parameter to define whether or not public holidays on weekdays shall be shifted to the closest workday. The formula for Independence Day would then look like this:

DATETIME(STR(YEAR([Date]))+”-7-4″)+

IF [Shift to closest workday] THEN

IF DATEPART(‘weekday’, DATETIME(STR(YEAR([Date]))+”-7-4″)) = 7 THEN

-1

ELSEIF DATEPART(‘weekday’, DATETIME(STR(YEAR([Date]))+”-7-4″)) = 1 THEN

1

ELSE

0

END

ELSE

0

END

All other fixed holidays would be calculated with a similar formula.

A Calendar including US Public Holidays

Here is an example putting all this to use: a US calendar including the public holidays for the years 1900 to 2099 on Tableau Public:

The workbook contains 3 additional Calculated Fields:

[Date Description] assigns the name of the holiday (and “Saturday”, “Sunday” or “Work Day” if the day is no holiday) to the dates using a CASE statement and an IF clause. The field is used in the tooltips.

[Date Type] assigns “Work Day”, “Weekend” or Holiday” to the dates, also using a CASE statement. [Date Type] is used to color code the days in the calendar.

[Holiday Definition] assigns the definition text to the holidays. The field is used in the list of holidays at the bottom of the dashboard and in the tooltips.

What’s next?

The rules of US public holidays are rather simple compared to other countries. In Germany, for instance, many public holidays depend on the date of Easter Sunday. The challenge: calculating Easter Sunday requires a pretty complex Calculated Field. But it is possible. One of the next articles will provide the same workbook as shown above, but this time for a German calendar.

Stay tuned.