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

8 comments on “Tinkering with Johan’s Unified Dates

  1. Kris says:

    Shawn, I got a page not found for the 2nd link.

    Like

  2. Thanks Kris, it should be fixed now. –Shawn

    Like

  3. Kelly says:

    So helpful and timely! Thank-you!!!

    Liked by 1 person

  4. David says:

    Excellent post – I’ve been playing around and modified your example with a version that lets you pick a particular individual date to use as a reference end point.

    There are some problems I’ve not overcome such as how this might affect leap years, and got me wondering about how you might accomplish this using an actual date picker for the reference date picker, rather than an integer mapping?

    Like

  5. Guille says:

    Hello Shawn, can you do the same with a cube? With DMX and calculated members?
    I have tried but date filters in cubes are impossible to make them work.
    Thanks

    Like

    • Probably not. But then I don’t know/use Cubes. I do know they are very different in structure from flat files. And so many of the ‘possiblities’ are complex (three dimensional). Ask your question on the forums and see what they say.

      Cheers,

      –Shawn

      Like

  6. Guille says:

    Hello Shawn, can this be done with cubes (MDX calculated members)? I am having a lot of problems with dates when I have a cube database.

    Like

  7. Alaa Wadi says:

    Hi – this is great and allowed me to plot the correct graph! I have 2 questions – will this affect trailing 7-day lines? Also, my tooltip shows June 7, 2000. Since the graph spans several months, I’d like to keep the date field in the tooltip but not if it shows the wrong year (based on my new calculation).

    Like

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