Mastery can be magic. I can remember a time as a young person when I just started to have a clue about how music was made hearing an Eddie Van Halen solo on the radio and wondering, “How does one person make that many notes???” Or the scene in The Empire Strikes Back where Yoda lifts the X-Wing out of the bog and Luke is hornswoggled. Amazement, disbelief, and “I want to do that too!” all rolled into one feeling, when the heretofore impossible becomes, for an instant, possible.
One of the things Tableau doesn’t do is let you draw line charts with dashed lines, to create a view like this that could be from Excel or another application:
Except that Tableau can do this, and you’re about to learn how – actually, three entirely different techniques. Along the way you’ll learn some more about how Tableau draws Line Marks and table calculation domain padding.
When we’re building a view in Tableau, we’re set up by the interface to think about moving this pill to that shelf. Just like when picking up a guitar, one hand is on the frets and another on the strings and we’re set up to start picking or strumming. It takes a willingness and curiosity to see and do things differently to start tapping away on the frets, or to get around the limits of size and mass (easier to do when 800 years old are you, hmm?).
One of the keys to Tableau mastery is to stop thinking “What pills do I need in the view,” or “How do I format the pills in the view,” and instead use a different approach:
- Understand your goal.
- Understand your data.
- Figure out what the ideal data structure and values – given an understanding of how Tableau works – would look like to reach that goal.
- Then and only then figuring out what calculated fields, dimensions, measures, and/or reshaped data would lead back to #1.
- Build the view. Now you start dragging and dropping pills.
When we start thinking like that, the view becomes a canvas on which we can paint with marks, labels, annotations, reference lines, and more and the limits of “this can go here, but not over there” go away and instead become sources of creativity.
1. Understanding the Goal
In this case, the goal is pretty clear: A time series line chart using dashed lines, showing the sum of sales for each month for a dimension.
2. Understanding the Data
I’m using Superstore Sales in this example, so we’ve got a nice data set. Key things to know for Tableau are things like the dimensionality of the data, the “cardinality of the result set” – aka fancy words for how many unique values there will be for a given set of dimensions, what makes for a unique record, data types, and ranges of values. Tableau can be a great tool for exploring your data structure as you drag and drop pills, move them around in the view, and visually interact with the data.
3. Figuring out the ideal data structure and values – given an understanding of how Tableau works
The second part of the above statement is probably more important, and it’s like that old joke: “How do I get to Carnegie Hall?” “Practice, practice, practice.” The Tableau apprenticeship is to master the the ins and outs of different mark types, the continuous/discrete and aggregate/non-aggregate variations, reshaping data, table calculations, etc. One of the best ways to learn Tableau is to take apart a workbook and then duplicate it yourself. Not just looking at the workbook to see how the magic was done, but actually re-recreating it yourself, so you can embed the “move this pill onto that Shelf results in X happening” knowledge into your muscle memory. Also, making mistakes while rebuilding workbooks has been hugely educational for me.
For this example, the “aha” moment (over breakfast) was that all I needed to do was draw a line, then erase some values. That requires an understanding of how Tableau draws lines. When we drag something like Month(Order Date) and SUM(Sales) into the view, Tableau generates a point for each Month of Order Date in the data:
We can get a sense of this in the status bar at the bottom of the Tableau window, Tableau tells us how many marks, and how many panes (via the rows and columns):
Tableau is drawing 12 marks, and connecting the points. All Tableau needs to do to draw a line is two marks, one for each end of the line, so what we’d need to generate to make dashed lines would be a whole lot more marks in the right places.
4. Figuring out what calculated fields, dimensions, measures, and/or reshaped data would lead back to #1.
So, we need a bunch more marks. Where can we get more marks? Padding. We can pad the data outside Tableau using Custom SQL, a custom query in our data source, manual calculations in Excel, etc. Or we can do it inside Tableau using table calculation domain padding, which is what we get when we turn on the Show Missing Values switch for a date or Bin. For example, we can pad monthly data into daily data, and that would give us 28-31 values per month to work with, which seems like it would make a reasonable number of dashes. In the Superstore Sales data, though, the data is already at the day level and excluding every Nth day would be removing data from the view. So, here’s what I did:
- Created a field that truncates the date to the month level.
- Use that month of date in the view at the day level.
- Turn on Show Missing Values to fill in the missing days.
Here’s what that looks like in a crosstab:
This is a good example of the kind of thinking I’m advocating here – we’re taking control of the level of aggregation used in the calculated field. An example you might have already used is in building a set for nested sorting.
I’d thought about drawing just enough marks to make the lines, but that would involve a whole lot of calculations just to figure out where we were in the dashes. So instead I decided to just use up more CPU cycles, do all the calculations for every padded mark, and then erase every Nth mark. The basic algorithm is:
- Pad out the sum of sales so that is available to every day.
- Determine next month’s sales and pad that out to every day. While I could have used a table calc or Custom SQL here, the easier route was to do a self-blend on the Month of Date, only the Month of Date in the secondary is from the next month. Since Tableau can blend on any dimension, calculated or otherwise, using a calculated field for blending can be a useful trick.
- Figure out the number of days in the month and pad that out to every day.
- Generate an index within each padded month for every day.
- Use the calcs from 1-4 to generate stepped daily sales value so the lines will have the right slope.
- Set every Nth value to Null. I set this up using a parameter, to make it easier to control. 4 is a good value for dashed lines, smaller can get you dots.
For the solid and dashed crosstab, I also added a parameter to choose which line would be solid, and a couple of other calculated fields to return the proper values based on whether the line would be a solid or dashed.
The particular lesson here is that even though Tableau has a SUM(Sales) measure, and an easy way to draw the Month of the Date, in order to generate the desired results we’re using our own derivations of those fields. At this particular moment, we’re not just thinking about Tableau as a tool for enabling the cycle of visual analysis, we’re also thinking about Tableau as a drawing engine. The bit of Tableau Zen here is to focus on what the data needs to look like for Tableau to generate the desired outcome.
Here’s a screenshot of the crosstab for the solid and dashed lines version:
I’ll leave the details to your own exploration, and only add a couple of notes: Tableau’s densification operations like table calculation domain padding can do some pretty amazing things, but something that they don’t do give us access to the dimensions that are used in creating the padding. This is due to Tableau’s order of operations – all row level calculations, and almost all aggregates are computed in the underlying data source before densification occurs. For example, MIN([Month of Order Date]) is computed in the data source and does not return values for the padded data:
Therefore, we need to use table calculations to interpolate the values, like this formula to get the padded day of the Month of Order Date:
DATEADD('day',INDEX()-1,TOTAL(MIN([Month of Order Date])))
A similar case exists for the Category that is used to color the lines, it’s also not available in the padded data, so this calc is used to generate that:
5. Building the View – Dragging and dropping pills
Once the crosstab was working and the calculations were set up, it was time to build the view by duplicating the crosstab and moving pills around. Going through the first four steps of this makes this process quite easy, whereas starting with the blank sheet and moving pills around would be much more difficult, if not impossible. Oftentimes, when things are difficult in Tableau it’s either a lack of knowledge or a data structure that’s not ideal for Tableau.
The two settings necessary to make the dashes were to use a Continuous (green) date, and to set the dashed lines pill to have Format->Pane tab->Special Values section->Marks combo box set to Hide (Break Lines). If you are actually going to use a view like this, you may also need to play with the date axis tick marks so they show the proper aggregation. In addition, I organized the tooltips so instead of displaying the sum of Sales value used to plot the mark, they display the sum of Sales for that month. Here’s the Tableau workbook:
Something you might have noticed is that the dashes don’t have the same length. They are longer when the difference between one month and the next is bigger. This is because of the algorithm I’d used, making an algorithm that would end up with equal length dashes is theoretically possible, but beyond the scope of this demo.
There you have it…dashed lines in Tableau, with a dash of densification. Now for a variation and a couple of alternative solutions for creating dashed lines.
Area Chart Variation
Here’s an area chart made from the above view, personally I like how the shape of the area chart is still there, yet the time element is somehow more visible here, especially when hovering over the view.
The Tableau Jedi Quiz Solution
A couple of weeks ago I posted the screenshot of this to Twitter (link, pic), and Dustin Smith – Tableau’s Community Manager – responded that Marc Reuter – the Director of Sales Consulting at Tableau who’s famous for his Tableau Jedi talks at the Customer Conferences – had a technique for dashed lines that he’d use to stump new employees. I got a little hint from Dustin and put this together, it’s using the dashed line History from the Pages Shelf:
One advantage to this solution is that you don’t need to know table calculations to put it together. An aesthetic advantage is that all the dashes are the same length. Two disadvantages compared to the table calc solution are that you can only do dual axis charts with bars in this one because the Pages History is turned on for all marks in the marks in the view, and that you are really only showing one set of Marks (for the last value on the Pages Shelf) so the tooltips are not accurate.
The Tortured Trend Line Solution
This is a case where playing around in Tableau can lead to serendipitous-and-sometimes-impractical techniques. If your data has *just* the right values, you can create a line, apply a polynomial trend line, and make the original transparent:
While I’d never use this in practice, it’s a demonstration of the idea that we don’t have to be limited by pills on Rows and Columns.
This post started out with a call to change how you look at the Tableau workspace, your data, and your visualizations. With some practice, you’ll be able to do this yourself. While a few of the particular techniques used here are advanced, they are all within reach and fast to implement – after having the seed of the idea, I had all the elements of the view together in under half an hour while riding the bus to work. The first key is to take the time to use, understand, and experiment with the different features of Tableau. If the data you work with daily doesn’t have much variation, then go seek out more to explore. The Tableau Forums are a tremendous resource for this, with a huge variety of user-submitted problems and answers. The second key is that there is that there is no pill.
(Sorry, couldn’t help myself with that Matrix reference there.) Seriously, though, mastering building views in Tableau is about learning the fundamentals well enough so they are in memory, then forgetting them so you can build something magical.