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 (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:
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.
Great post! Very educational!
Thanks! I really hope it helps clear up some of the confusion and frustration that some have felt.
Great! Very helpful. Thank you! 🙂
You’re welcome and thanks for the kind words!
Thank you! This just saved my sanity after endlessly struggling with dates. The simple explanation made it click!
You’re welcome, Katie! I’m very glad it helped!
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. 🙂
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.
Thanks, Josh!
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.
This is simply the best stuff you need to know to graduate to next level in Tableau.
Simply great work.
Thanks a ton!!
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.
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.
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.
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
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().
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.
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])
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!
Thank you Jim! I’m glad it helped!
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
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
I can’t think of a way right off. If I do, I’ll let you know!
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?
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!
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.
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.
Really helpful.
Thank you!
I’ve been working with BI tools for 20 years – Tableau was not the first to have built in date dimensions with date parts. Business Objects had it 10 years before Tableau.
Tableau is rather difficult to work with in comparison to other true BI tools.
For example, why does Tableau assume you always want to display the “Year” when applying a date to the row shelf. I find this feature extremely annoying and time consuming. If I wanted the year, I would put the year on the row shelf. No, I want the whole date, every time.
Alas, tools that claim they are years ahead of other tools frequently aren’t.
By the way, where is the distribution and scheduling feature?
Qlikiew has one, but it is woefully lacking and not ready for prime time.
Tableau doesn’t have one either – but Business Objects does…..and they really were the first.
Wavy,
Good catch – and poor wording on my part. To the best of my knowledge, the innovation Tableau brought was the ability to split various parts of the date hierarchy across different shelves; that is, different parts of the date dimension could be used to encode very different parts of the visualization. I may even be wrong about that, as I’m going on my memory of a second-hand conversation. Thank you for the fact-check!
Thanks for the post. It’s really useful. I’m also having headache dealing with dates in Tableau. Especially with Gantt Chart and continuous date filter.
Let say I want to create a date filter (let say start date of the project) and I use the option ‘exact date’ and ‘continuous’. There are hundreds of projects with the start date varies between 01/01/17 up to 17/01/19. When I put that filter into the dashboard, it shows the time span between 01/01/17 up to 17/01/19. On the filter I choose ‘Only relevant value’. However when I do drill down by selecting some projects that started between 01/01/18 up to 01/05/18, the start date filter doesn’t move according to the specific time span (which is 01/01/18 up to 01/05/18). It stays on 01/01/17 up to 17/01/19. I’m confuse what I have done wrong. But when I change the filter into discrete (and make single value drop down – it looks ugly btw), it drills down nicely following the start date of the projects that I have selected.
Could you please advise what I have done wrong ?
Thanks. It’s really appreciated.
I was struggling with how to get ordered chronologically and this really helped. Thank you.
You’re welcome!
Our catch phrase around the office is “Tableau makes the easy things hard and the hard things impossible”, and this is such a great example. Why does one have to hunt this information down on the web?? Why does Tableau just blankly present you two sets of nearly identical options without a clear reference describing the difference? How much extra menu space would it take up to add sub-headers “Date Part” and “Date Value” to those identical sections?
I stumbled in here because I’m looking for some explanation as to why Tableau just stares at me blankly when I try to drag my date to before my other pill. I want to group by date -> shift. But it just stubbornly refuses, offering no help, doing nothing. In SQL this is dead simple – “group by date, shift”.
In tableau, I’m going to have spend god only knows how long trying to suss whatever arcane incantation Tableau needs from me so that it can feel emotionally fulfilled and do what I’m asking.
🙂 I have to admit that Tableau’s interface isn’t incredibly clear on this and most of the training material and books muddle the issue (shameless plug for this book that does address the topic: https://www.amazon.com/Learning-Tableau-2019-Intelligence-analytics/dp/1788839528/)
Great Post – Thanks for taking the time to document and share.
Is There any way to change the date field label in tableau
Parag, often what I’ll do for discrete date values that come with overly verbose labels (like “Month of Date” or “Year of Month”) is to hide the row/column labels and instead add an ad-hoc calculation that hard codes the value I would like to see as a label. For example, if I have Order Date on Columns as a Discrete Date Value I would hide the Column Labels and instead add an ad-hoc calculation to the left of the field on Columns with the code “Order Date”