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

    “And tear out my beard and uncover my head!” -Tevye

    “And tear out my beard and uncover my head!” -Tevye

     

    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:

    Wrong

     

    Here’s what I think when I see that:

    No!  A thousand times, no!

    No! A thousand times, no!

     

    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:

    View with Discrete Measure

    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:

    Discrete and Continuous Options

    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):

    never finding the right view

     

    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:

    Discrete field in action

    Continuous (green) fields in action:

    Continuous field 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:

    Options

     

    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.

    Then I am a Jedi

    “Then I am a Tableau Jedi.” “No. Not yet. One thing remains. Dates. You must confront dates.”

     

     

    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:

    Date Menu

     

    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

    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:

    November

     

    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 (1st through 31st).  The 8th would have been any 8th 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:

    Continuous Date Parts

     

    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.

    “You want to be continuous”   “I want to be continuous”

    “You want to be continuous” “I want to be continuous”

     

    Date Values

    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 Value Bar Chart
    • 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.

    Qui Gon prefers Continuous while Obi Wan likes Discrete.  I prefer Episodes 4 - 6.

    Qui Gon prefers Continuous while Obi Wan likes Discrete. I prefer Episodes 4 – 6.

     

     

31 Responsesso far.

  1. Great post! Very educational!

  2. Joshua Milligan says:

    Thanks! I really hope it helps clear up some of the confusion and frustration that some have felt.

  3. Tableau User says:

    Great! Very helpful. Thank you! 🙂

  4. Katie Tschudy says:

    Thank you! This just saved my sanity after endlessly struggling with dates. The simple explanation made it click!

  5. Sophie says:

    Very good article. What I can’t be seem to be able to do (not possible in Tableau) is to change a Date from Dimension to Measure, so that I can do conditional formatting on Dates – as a matter of fact, I have multiple Date fields as Dimensions that I would like to place side by side in the ‘abc 123’ label and it is not possible. 🙁 So while I still agree with you that dates can be highly manipulated in the analysis, it still leaves a HUGE gap in terms of conditional formatting for Dates. Most other fields you can move between Dimensions and Measures as you mentioned. 🙂

    • Joshua Milligan says:

      Thank you, Sophie!

      I think you can get what you want — though it takes using other continuous fields to define multiple axes so you can adjust the formatting for each independently. You should be able to get something like this:

      . You can get the workbook here.

  6. Ketih says:

    Thanks, Josh!

  7. Bruce Segal says:

    Josh: Thanks for making it clear that the 1st section of dates is for Date Parts, and the second section for Date Values. Once you made it explicit it hit me in the head with a klump! I had been working with them, switching btwn discrete and continuous, w/o realizing they were set up to make that distinction.

    I suspect part of the problem you mention about Tableau users wrongly teaching each other that one is discrete and the other continuous may be tied to the historical development of Tableau. Those 2 sections for dates replaced sections called continuous and discrete dates in versions 3.0, 4.0, and maybe 5.0. I think the deal was that date parts could only operate as discrete values. I do know that before that change in dates we could not get trend lines for discrete date.

    When Tableau made the change it added those 2 sections, but didn’t add any into to make it clear that one section was for date parts and the other for date values.

  8. Karan says:

    This is simply the best stuff you need to know to graduate to next level in Tableau.

    Simply great work.

    Thanks a ton!!

  9. Moody says:

    Hi, Can I double check these concepts and date functionality is still applicable when connecting to an SSAS cube datasource with Tableau? To my knowledge it does, I have happily been able to work dates from an SSAS cube in this manner but there are developers who find dates easier to manipulate from a non-cube source but personally it think this is a perception rather than a real issue as you’ve proven above.

    • Joshua Milligan says:

      Good point, Moody. The date part options do not appear for dates in cube data sources. Instead, you’ll need to build the cube to include the various date parts/hierarchies.

      • Moody says:

        Hi, is there a way I can email you to explore this subject further? I’m not on social media …

        I have a further question on this. We have created cubes with all the date parts, datetime and hierachies. Because Tableau does not automatically observe them as datetime format but string the visulisations that require a ‘date’ in the showme menu are turned off. If we change the format to datetime and set them to continous then we get odd behviours with values put in the NULL category and the dates don’t show as you would expect. I know there are workarounds to getting the ‘date’ visulisation options outside of the showme menu but there is no reference on how to do this using cube discrete date dimension and an explanation of why changing to datetime and continous causes erroneous results. I find it takes alot of moving around and playing with to make your visulisation do what you want it to, sometimes to no avail. It certainly impeeds business training. Can you advise if you have experienced this, if you have an easy method of using cube dates with the date visulisations as I know you quite enjoy using cube sources in Tableau and so do I but this is a frustration.

  10. Alex Blakemore says:

    Joshua,

    Very useful post on a topic that confuses everybody. Thanks.

    One very minor technical nit, what you are calling date parts are actually date names (if we go by the behavior of the Tableau functions DatePart() and DateName())

    The only time there is a difference between the two functions is with months. DatePart(‘month’, #4/15/2016#) = 4 while DateName(‘month’, #4/15/2016#) = “April”

    Date part is probably a better term to use for explaining the behavior, but it might be worth a footnote for anyone working with calculated fields.

    Alex

  11. Alex Blakemore says:

    P.S. A correction to my previous reply. If you choose a month level date part from the menu, and then change the date to continuous, Tableau will return the numeric date part instead of the date name. So it is apparently only discrete month level dates that act like date name() instead of date part().

  12. DG says:

    Hi, Josh —
    I’m an extreme newb. I’m working with a view here:
    https://community.tableau.com/thread/130384

    for calculating the date since a page was last visited.

    Everything is connected and running, but the data seems wrong. I’m working with a huge site with many realtime visitors. Tableau is showing no fewer than 5 days “since the last visit” for any given page — impossible since GA realtime is showing dozens of visitors right now.

    What am I missing? Shouldn’t these “days since last visit” counts be starting with 0 and accumulating from there for all of the pages?

    Thanks very much.

  13. Date Smells says:

    Anyone know if there’s a way to determine the level of date hierarchy from a calc? i.e. viz has YEAR([Date]) but user has drilled down to MONTH([Date])

  14. Jim says:

    Thanks for this post. I really helped me get my brain around what tableau is doing with dates. I found this after having an unrelated issue involving bringing AS/400 (or did I mean iSeries) strings and numbers in as dates but it answers many questions that have been percolating through my brain. Well written and very helpful. You RAWK!

  15. Danielle Strothers says:

    Hi Josh,

    Thanks for this, very helpful.

    I have 2 data sources on a worksheet, both with a date dimension but at different levels (i.e. one at month and one at quarter). I’m looking to add just one filter that controls them both.

    I’ve therefore created the relationship on all levels of the date, and then chosen the date value section ‘Q1 2016’ for example.

    It’s nearly working but it appears my secondary data source is bringing through Q1 2016 data AND Q1 2015 data – So it’s not quite working on the year.

    Do you have any ideas on where i’m going wrong?

    Thanks,
    Danielle

  16. Gavin Attard says:

    Hi, This was really helpful.

    I however have one beef with dates that still drives me nuts and has to do with the way it is displayed on axis.

    Is there any way that i can format the axis display to show month on one line and year under that but displaying only once? (a bit like excel does it)

    I can get that when i am using my year and month dimensions, but then i loose the continuous aspect and get cut lines…

    Cheers

    Gavin

  17. PB says:

    Good educational write up, thank you.

    I have a question though…I have a data set with dates and other dimension and measures. and we don’t have all the dates (for some dates we don’t have anything at all).
    Now, I created a viz which will display one dimension count in Size like traditional calendar style. Since we don’t have all the dates in our data, some of the dates were missing (obviously). how can I display the dates also even though there is no date in the data?

    • benmelek_admin says:

      PB, When using a date dimension on Rows or Columns except for using the field as an EXACT DATE when the field is a datetime type), then you should be able to check “Show Missing Dates” from the drop down of the field to show any missing values. If you are not using the date on Rows or Columns, then it gets a bit trickier and you might have to use other data densificaiton techniques or even shape the data at the source. Hope that helps!

  18. Pramagatist says:

    Great Information – but the PRIMARY issue I have with TABLEAU dates is; When I use a date as a Dimension which it always is, then I must format to mm/dd/yy (American Format). Not an issue – however Tableau then decides that the Label for said date must be changed to Month Date Year of ABCDEFG.

    Seriously! THe label is the field name PERIOD!

    THIS IS WHAT DRIVES ME UP THE WALL and should not be so f’ing complicated to work around.

    • benmelek_admin says:

      I hear you. I wish the formatting was a bit easier and I also find myself changing (or removing completely) the label of a date axis. However, I guess I’d rather have to think through and be deliberate about the label (or lack thereof) than have a potentially misleading label automatically applied that doesn’t make it clear that the view is aggregated at a given level (e.g. month, year). Many views are obvious – but not all.

Leave a Reply

Your email address will not be published. Required fields are marked *