Complex Fiscal Calendars in Tableau

Tableau has the option to specify the month that a fiscal calendar starts on, and the day of the week that a calendar starts on. This adjust the Year and Quarter that a given date belongs to in the date hierarchy. If your fiscal calendar is more complex,  you’ll need to do some adjustments to your dates to make everything work smoothly in Tableau.

Sometimes I put myself a note to write up a topic and lo and behold! there is already great content out there.

The standard solution, much like the currency conversion example, is to have a fiscal calendar mapping table, which you JOIN to your data. In essence, you have a row for each “real” date in the calendar,  with the information about where that day falls in the fiscal calendar (year, day, month, etc.) in additional columns. You’d base your time filters in Tableau on the fiscal date columns. There’s already a great example of this method on the Tableau Community. The example uses an Excel table, but obviously you would want that table in your database if you are bringing data from a database. Cross-database JOINing in Tableau 10 may let you keep the two separate.

The toughest trick with a complex fiscal calendar is how much it can diverge from the “real” calendar (although — is time really real, man?) Particularly for continuous date filters, the fiscal date might be really far off. If FY 2016 Month 1 Day 1 is actually July 15th, 2015, you don’t really want to show the “real” date, even if you want a continuous filter. I recommend creating a continuous integer that reflects the fiscal calendar, but not converting it into a date field: 20160101 will still be understandable and work efficiently for continuous filtering, but it won’t be confused for the “real” date.

InterWorks also has some great write-ups of solutions using calculations, that don’t require the mapping table:

Creating Dynamic Fiscal Calendars in Tableau

Creating Custom Fiscal Year Calendars in Tableau

One last thing to note, when publishing templates where the fiscal calendar start month or week start day varies from customer to customer: The “Date Properties…” menu in Desktop simply adds the following XML to the very end of the datasource XML, where it can easily be modified:

<date-options fiscal-year-start='august' start-of-week='friday' />

[/xml]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s