Dates in Tableau make me want to tear my hair out!

• By Joshua Milligan
• Posted in All, Calculations, Dates, Storytelling & Commentary, Tableau Tip Tuesday, Tips and Tricks
• On April 14, 2015
• 39 Comments.

 

Actually, it’s not dates in Tableau.  Tableau does wonders with dates!  What other tool allows you to connect to a data source with dates and have a built-in hierarchy where you can move different parts of the hierarchy around in the view?  What other tool has its own built-in date dimension?  There are imitators – but Tableau did it first.

(That’s not to say there isn’t room for improvement)

 

But it isn’t dates or using dates in Tableau that drives me nuts.  So, what is it? It’s the way people have been taught to think of dates in Tableau.  It drives me nuts, because ultimately it drives them nuts and they get frustrated and complain and think Tableau is hard and needlessly complex.

But it’s not Tableau – it’s the way they’ve been taught or the way they’ve made assumptions.  And actually, it goes deeper than dates.  It starts with dimensions vs. measures, and discrete (blue) vs. continuous (green).

 

Dimensions, Measures, Discrete, and Continuous

Here’s the issue: in an attempt to keep things simple in explanation, accuracy was sacrificed.  Consider the following:

 

Here’s what I think when I see that:

 

To say dimensions are discrete and measures are continuous is a simplification.  Simplification can be good.  But not when it’s just flat out wrong.  It might appear to be helpful, but really lays a poor foundation of understanding that will limit people from moving forward.

Consider this view:

Not only can I see the number of habitable planets for the Klingons, Romulans, and Federation, but I can also quickly see a count of Neutral Zone Violations as headers in front of the bars.  I can do this using a Discrete (Blue) Measure. Yes: it is a measure.  Yes: it is discrete.  Let that sink in.

It. Is. A. Discrete. Measure.

I made it discrete using the drop down menu on the field:

If I didn’t know I could use a measure as a discrete field in the view, then I might have moved the continuous field all over the place, searching for the perfect view (but never finding it):

 

So, let’s keep it simple.  But let’s also keep it accurate:

• Measure: a field that is aggregated (e.g. summed up, averaged, counted, etc…).   For example, I can sum up all my Habitable Planets, take an average, or get the minimum or maximum.

vs.

• Dimension: a field that slices or groups the measures.  For example, I want to get the sum of Habitable Planets and the sum of Neutral Zone Violations sliced up by Alignment.  Since there are 3 alignments, I’ll get 3 sums (SUM of Habitable Planets and SUM of Neutral Zone Violations for Klingons, for Romulans, and for Federation)

 

Now, contrast that with:

 

• Discrete (blue): Individual Values – when a field is used as a discrete field in the view, it will define headers and color legends with individual values.

vs.

• Continuous (green): A  Flowing Range of Values – when a field is used as a continuous field in the view, it will define axes and color legends with gradients:

 

Discrete (blue) fields in action:

Continuous (green) fields in action:

 

There’s some complexity we could dive into.  But we can keep it simple – and without sacrificing accuracy.  Here’s the simple thing to remember:

 

Measure vs. Dimension determines what fields are being aggregated and what fields are slicing/grouping the aggregations.

Discrete vs. Continuous determines how Tableau draws the results (headers, axes, color legends, etc…)

 

And here’s the secret:

For the most part it doesn’t matter whether a field is in the Dimensions list or the Measures list in the left Data Window.  That’s just the default, but once the fields in the view, you can use the drop down to convert almost any field back and forth between Measure and Dimension and between Discrete and Continuous:

 

In summary: Almost any field in the view can be used as either a Dimension or a Measure.  And numeric and date fields can also be used as either Discrete or Continuous. 

 

And with that, you’re on your way to becoming a Tableau Jedi.

 

 

Dates in Tableau are not hard…

…at least not if you take some time to understand and use careful, precise terminology.  When you right-click a date field in the view in Tableau, or use the drop-down you’ll get a menu like this:

 

Now admittedly, this is a long list and it might be intimidating at first.  But let’s break it down.  There’s:

• The standard options for a field (Format…, Show Header, and at the bottom: the Tableau 9.0 feature – Edit in Shelf, and Remove)
• Show Missing Values
• A section of Date Parts
• A section of Date Values
• Exact Date
• A section that allows you to switch between Attribute and various Measure aggregations
• A section that allows you to switch between Discrete and Continuous

We’ll skip the standard options and merely mention, for now, that Show Missing Values is simply a way of telling Tableau to include missing dates in a visualization so you can see gaps in time where there is no data.

And just like almost any field, we can use it in the view as a dimension or a measure.  And we can use it as discrete (blue) or continuous (green).

 

The two primary sections that need to be understood are the two sections of dates.  The first section is a list of Date Parts. The second section is a list of Date Values.

 

Date Parts

Date parts are just the individual parts of a date – all by themselves.  So a date of November 8, 1980 could be broken down into individual parts:

• Month: November
• Day: 8
• Year: 1980

And when I use just one of those parts as a dimension in a viz, then I’m telling Tableau, “Aggregate all the measures at the level of that date part.”  So, for example, when I put a Date on Columns and then select the Date Part of Month, I might get a view like this:

 

I’m telling Tableau, give me the SUM of sales for each Month (as a date part).  Sales on November 8, 1980 are included in the bar above.  So are Sales on November 9, 1980.  And November 22, 2015.  Sales in any November are included.

If I had selected the Date Part of Day then I would have a bar chart of every day (1^st through 31^st).  The 8^th would have been any 8^th of a Month: November 8, 1980; December 8, 2015; February 8, 2025.

 

Now, some people have been taught to refer to Date Parts as “Discrete Dates”.  This is because when you select a Date Part for a date field, the default is a discrete (blue) field.  But it is not accurate to speak of Date Parts as “Discrete Dates”.  You can switch a Date Part between Discrete and Continuous.  Here’s that view from above after using the field’s menu to make the switch:

 

Sure, the way Tableau drew the view changed (an Axis instead of headers, formatting the month as a numeric value, making the bars narrower, faint grid lines) – but fundamentally, it’s still giving the SUM of Sales for all Novembers (and Januarys and Februarys, etc…).

Don’t think of Date Parts as “Discrete Dates”.  They are “discrete by default’, but the Tableau Jedi can merely wave his hand (or use the right-click menu) and change things as desired.

 

Date Values

Date values are indeed the value of the date.  Notice that it isn’t just November.  It isn’t just 8.  It’s November 8, 1980.  Or, it’s November 1980.  It depends on the level you select.

So take that date and assume it has time included: November 8, 1980 5:46am

Depending on what level of detail you select you’ll get a value that is truncated (lower levels of details getting cut off).  Let’s rearrange the date and pretend we’re building a bar chart so we can see it better:

Year Month Day Hour Minute Second

1980 November 8 05 46 00

• Year: 1980 November 8 05 46 00 – Truncated to the Year. Any date that falls within 1980 will give us a single bar.  There will be a bar of every other Year in the data.
• Quarter: 1980 Q3 November 8 05 46 00 – Truncated to the Quarter. Any date that falls within 1980 Q3 will give us a single bar.
• Month: 1980 November 8 05 46 00 – Truncated to the Month.  Any date that falls within November 1980 will give us a single bar.  Notice we get a bar for each Month and Year.  Notice also that I’ve made the field discrete in the view, which gives headers.
  
• Date: 1980 November 8 05 46 00
• Hour: 1980 November 8 05 46 00
• Minute: 1980 November 8 05 46 00
• Second: 1980 November 8 05 46 00

Again, some people have been taught that these are “continuous dates.”  They are not.  They are continuous by default.  But you can change them as needed.

Exact Date

Not much to say, except that this is the exact date value in the data.  No truncation, no funny business.  Just the exact date to whatever precision the data contains (including time if applicable).  This one can also be either discrete or continuous (it is continuous by default).

 

Why it Matters

Because you will no longer get frustrated and wonder why dates are so hard and confusing.  They’re not – not really.  And you will be able to generate some really cool views and impress your friends and co-workers.  You’ll select date parts or values as needed and change them to discrete or continuous at will.  You will be a Tableau Jedi.