Tinkering with Johan’s Unified Dates

Johan’s Unified Dates

Before the holidays Johan Sivertsen (Kettan) posted an interesting workbook on our Tableau forums as an answer to the often ask year-over-year comparison question. His solution was both elegantly simple, and complex — at the same time!

Unified Dates Orig

Here’s his original workbook. This is the formula he’s using:

DATE(DATEADD(‘year’, 2000 – YEAR([Order Date]), [Order Date]))

So let’s unpack this. First he’s using the DATE() function to convert the results from a Date-Time to just a Date. That’s pretty straight forward. (Also something I’d never thought about doing before, so this old dog thanks you for that new trick Johan.) But what’s going on with that DATEADD() function? Here’s how this function works:

DATEADD(date_part,increment,date)

In the Superstore sample dataset we all use, there are 4 years of data. By subtracting the YEAR() of each of these years from a constant, Johan was able to ‘unify’ all the years; essentially converting them all to a single constant year, in this case the year 2000. Since we are going to mask the year of the axis anyway, it really doesn’t matter what constant you use; except that it has to be between 401 – 9,999. Why? I have no clue; just another quirky little Tableau thing that’s fun to know. 🙂

Once we get that ‘unified’ year axis setup (and format it as MMM D or mmm), then it’s just a matter of dragging the original Order Date out onto the color/label shelves as a year part. The nice thing about this solution is it avoids a lot of date math and conditional calculations, and can be designed to be very flexible.

Very nice stuff Johan, thanks again for this!

Adding Fiscal Years

The original questioner wanted to have the year-over-year comparison start at any month of the year, essentially creating a dynamic user-defined fiscal year comparison. This is where I focused my tinkering — starting with Johan’s original workbook of course. Changing from Johan’s running sum viz to a straight SUM(Sales), here’s the viz I came up with:

Unified Dates Orig FY

Here’s where you can find the workbook.

You’ll notice that I’m using a parameter to let users select the fiscal year start month. If you Edit the Fiscal Year Start parameter you’ll see this:

FY Parameter

A couple of things to note. I’m using an Integer data type and then aliasing it with string values. Computers love numbers and hate strings, so integers execute much faster. Also by using numbers I can do the DATEADD() calcs I need much more directly. It took a bit of trial and error to figure out that -11 would give me a fiscal year starting in December, but that’s the true fun of tinkering around with Tableau, and of the forums in general for that matter. (FYI: Tableau cured my Sudoku addiction!)

After getting the parameter values worked out it’s just a simple matter of doing some date math to get it all working together:

Code

Also check out how the Year Labels calculation works. A common answer to questions on our forums is to separate the calculation that produces the marks, from the calc that labels the marks. This is a technique I use all the time. And points out the wonderful flexibility of our favorite software!

Also make sure you follow the additional links Kettan posted; they lead to some great solutions to other X-over-X issues.

Advertisements