Calculated Fields in Tableau for German Public Holidays
The previous post explained why color coding public holidays can be very effective for analyzing daily data, provided the Calculated Fields for all public holidays in the United States and included an interactive calendar for the US on Tableau Public.
The Calculated Fields for the US public holidays are pretty straight forward and not too complicated.
This can be different in other countries. And it sure is in my homeland. In Germany, all variable public holidays depend on Easter Sunday. And, if you read on, you will see that Easter Sunday requires a really complex calculation.
Today’s post provides the Calculated Fields for all German public holidays and a German version of the interactive calendar on Tableau Public.
Tableau Calculated Fields for German Public Holidays
Like the United States, Germany has fixed and variable holidays, too. However, the basic rule of a variable holiday in the US (“nth weekday of a defined month”) does not exist in my country. In Germany, all variable holidays are based on the date of Easter Sunday, i.e. a defined number of days before or after that date. And Easter Sunday requires a complex formula.
Fixed Public Holidays
Let’s start with the easy part. Here are the Calculated Fields for fixed holidays in Germany:
Neujahr (New Year’s Day): January, 1
DATETIME(STR(YEAR([Date]))+"-1-1")
Heilige Drei Könige (Epiphany): January, 6
DATETIME(STR(YEAR([Date]))+"-1-6")
Tag der Arbeit (Labor Day): May, 1
DATETIME(STR(YEAR([Date]))+"-5-1")
Maria Himmelfahrt (Assumption of Mary): August, 15
DATETIME(STR(YEAR([Date]))+"-8-15")
Tag der Deutschen Einheit (Day of German Unity): October, 3
DATETIME(STR(YEAR([Date]))+"-10-3")
Allerheiligen (All Hallows’ Day): November, 1
DATETIME(STR(YEAR([Date]))+"-11-1")
1. Weihnachtsfeiertag (Christmas Day): December, 25
DATETIME(STR(YEAR([Date]))+"-12-25")
2. Weihnachtsfeiertag (Boxing or St. Stephan’s Day): December, 26
DATETIME(STR(YEAR([Date]))+"-12-26")
As already explained in the previous post, the calculations for fixed holidays is pretty straight forward: 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 Part 1: Easter Sunday
On the Gregorian calendar, Easter Sunday is the first Sunday after the first full moon on or after March 21.
I can’t tell you no lies: I had no clue how to put this rule into a formula. Fortunately I am having an Excel template in my toolbox with all kind of date calculations and this workbook also provides a formula for Easter Sunday. I would love to give credit to the genius who created it, but I simply can’t remember where I got it from.
Now that I had the solution in Excel, I simply dissected it and transferred the logic from an Excel cell to a Tableau Calculated Field.
Since the formula is very complex and one part is repeated 4 times inside the formula, I decided to split it into 2 Calculated Fields
[ES Intermediate] is the calculation repeated 4 times:
((255-11*(INT(YEAR([Date])) % 19) - 21) % 30) + 21
Based on this intermediate step, the formula for Easter Sunday (called [Ostersonntag] in my workbook) looks like this:
DATEADD ('day',
[ES Intermediate] - INT([ES Intermediate] > 48) + 6 - (INT(DATEPART('year', [Date]) + DATEPART('year', [Date]) / 4 +
[ES Intermediate] - INT([ES Intermediate] > 48) + 1) % 7), DATETIME(STR(YEAR([Date]))+"-3-1"))
I can see that it is working with the INT function and several modulo operations. But truth be told, I do not have the slightest idea how this is working and I can’t provide an explanation here. I am sorry.
Variable Public Holidays Part 2: All other variable holidays
As soon as you have the Calculated Field for Easter Sunday, all other variable holidays in Tableau are a walk in the park:
Karfreitag (Good Friday): 2 days before Easter Sunday
DATEADD('day', -2, [Ostersonntag])
Ostermontag (Easter Monday): one day after Easter Sunday
DATEADD('day', 1, [Ostersonntag])
Christi Himmelfahrt (Ascension Day): 39 days after Easter Sunday
DATEADD('day', 39, [Ostersonntag])
Pfingstsonntag (Pentecost / Whit Sunday): 49 days after Easter Sunday
DATEADD('day', 49, [Ostersonntag])
Pfingstmontag (Pentecost / Whit Monday): 50 days after Easter Sunday
DATEADD('day', 50, [Ostersonntag])
Fronleichnam (Corpus Christi): 60 days after Easter Sunday
DATEADD('day', 60, [Ostersonntag])
Not much to explain here, I guess. DATEADD('day', n, [Ostersonntag]) adds n days to the Calculated Field [Ostersonntag] (Easter Sunday).
A calendar including German Public Holidays on Tableau Public
Again, the workbook contains 3 additional Calculated Fields. Please refer to the previous post for the details.
Stay tuned.
Update June 1, 2015
I wrote above that I couldn’t remember where I found the Excel version of the Easter Sunday formula. Martin Nolan recognized it (see first comment below): Chip Pearson shares different techniques for calculating Easter Sunday, including the one I used (the shorter function): Easter Calculation - Chip Pearson
Many thanks go to Chip Pearson for providing the formulas (and all the other fabulous Excel solutions he shares for free) and to Martin Nolan for pointing this out. Thanks, gentlemen.