Over on the forum, Jeremy asked about making trellis charts. For more info on trellis/panel charts, John Peltier has a great article about them. Consider a case where you want to visualise two measures (eg sales and time) for a Dimension with many members, such as State. There’s no pretty way to do this in Tableau by default. Because there are lots of members in the State dimension (up to 50), you’ll end up with one of four things:
- A crowded line chart
- A very wide chart
- A very tall chart
- Something using filters to show just a few states at a time.
None of the above are ideal. Instead, you can use the principles of small multiples to arrange your dimension members into a grid. The workbook below shows the results.
Tableau loves small multiples. I’ve blogged previously about my most satisfying creation, which used small multiples. Tableau likes small multiples that are arranged with separate dimensions on the Rows and Columns. What we’re trying to do here, though, is arrange one dimension’s members arbitrarily on a grid. The data itself contains no information that tells Tableau which row or column it should appear in. Fortunately, we can use table calculations to create that information.
1. Create an index on the Dimension that you are arranging into panels
This is nothing more than an INDEX() function, set to compute using [Customer State]. It gives each State a numerical value, rising alphabetically.
2. Create a parameter to determin the number of columns
In the view above, you can change the number of column via the Parameter slider.
3. Create a calc for the Column value
To work out the correct column for each State, the calculation uses modular arithmetic.
The calculation has to check if the modular result is zero – if it is, then that member actually needs to be in the right-most column.
4. Create a calc for the Row value
For this calculation, we don’t use modular arithmetic, but use a ceiling function based on the [index]/[columns count]. Tableau doesn’t have a native CEILING function but Alex Kerin and Joe Mako came up with some on the Forum.
That’s all we need. You can then build the worksheet as follows:
I’ve done a bit of tweaking by hiding the Row/Col value headers – they are relevant to you as the report writer, but not the viewer. I also labelled the line ends to enable identification of each panel.
15 CommentsAdd Yours →
Andy, I really like this – it’s something I’ve been wanting to do for a while. This is the kind of functionality that would be great to be built into Tableau. If only we had someone on the inside…
Great job, Andy. The next step would be to order the panels by median sales or some other meaningful measure.
Good idea, Naomi. I’ve added a new tab to the workbook called “Panel Chart (sorted)” – the states are ordered by SUM(Sales). It would be quite simple to do this by a different measure – median or average, for example.
However, I think it doesn’t add to readability of the chart. If we have a straightforward bar chart, it’s good practise to sort it so the trend is obvious. However, the left-to-right-and-then-down-a-row nature of the trellis chart doesn’t, in my opinion offer that same possibility. Ordering alphabetically allows you to scan for a particular State very easily. Ordering by a measure makes finding a particular State very hard.
Andy, this is awesome. On behalf of all the Tableau newbies out there, thanks for sharing, and please do keep the tips & tricks coming! Ben @DataRemixed
Thanks for the response. I am getting close using the page that you sent. Although I am having issues with the Created Field for Column. It says that the
‘%’ function cannot be applied to table calculations and fields from multiple data sources. Any ideas of how to get around this?
@Jeremy – I commented on the forum page (link at the top of this post)
Very nice Andy.
I actually started putting the exact same example together yesterday to reply to Jeremy’s posting (i.e. Superstore Sales) – but got a bit stuck and then forgot about it. Prompted by your post I’ve just revisited it and worked out what was going wrong with mine – which is actually a nasty trap, so I’ll just explain briefly.
The index on [Customer State] works fine as long as there is at least one order per year per state. But if you want a more fine-grained timeline, it can become sparse, which means the index value doesn’t stay consistent for each state. I’ll post the version I was doing on the forum to show you what I mean. I got stuck looking for a reliable way around that (and am still stuck).
Also, you don’t actually need to get as tricky as that ceiling function – just this will do:
INT(([Index] – 1) / [Cols]) + 1
As expected, Joe has posted an explanation and resolution for the trap I had fallen into, over on the forum.
The reason for my suggestion was that you called these charts “panel/trellis charts”. Bill Cleveland introduced trellis charts and his panels were ordered by a measure. Kelly O’Day coined the term “panel charts” since he thought that the term trellis was trademarked; his charts are similar to Cleveland’s trellis displays. You might be interested in these references:
Becker, R, Cleveland, W.S., and Shyu, M.J. (1996). The Visual Design and Control of Trellis Display.
Journal of Computational and Statistical Graphics . 5(2): 123-155.
Cleveland, William S. (1993). Visualizing Data, Hobart Press, Summit, NJ.
O’Day, D.K. http://processtrends.com/toc_panel_charts.htm
Robbins, N. B. (2010), Trellis display. Wiley Interdisciplinary Reviews: Computational Statistics, 2: 600–605. doi: 10.1002/wics.121
Robbins, N.B. (2005). Creating More Effective Graphs. John Wiley and Sons. Hoboken, NJ.
Andy, Thanks for putting this together.
I felt like the labeling you used added too much additional white space, so building off your work, I made:
Joe, this is so good it hurts.
Andy / Joe — this *definitely* goes into my bag of tricks!
That’s a much tidier chart. The white space was a problem, and I considered trying to get around it with a dual axis/label approach, but didn’t go that far in the time I had to write this post. I notice that to acheive the tidy view, you’ve had to hard-code the label to year 2009. That’s good for this view, but how could you do it so it works for any length of time series, and one that might change over time?
Andy, there are a few ways to accomplish this depending on your situation. I have updated the workbook to calculate what year the label should be at. If your situation or constrains are different, another route may be necessary.
Joe and Andy,
Both you guys are great!
Your contributions to the Tableau user community and mad Tableau skills have saved myself and many other Tableau fans countless hours of effort. I’ve been wanting to create panel charts for some time now in Tableau, and a it looks like you guys have, as usual, provided a solution not just to tackle the main problem, but went ahead and added extra value to the equation. Your desire to help others by so openly sharing your work makes the world a better place.
Keep up the great work guys, and thanks!
Hi Andy/Joe or others,
Love this post on Panel/Trellis Charts, but I was wondering if you could help me understand a behavior:
I’m trying to find a way to use this to create something like a highlight table with just two colors. Let’s say for example: States that begin with the letter A = “Yes” or “No”. Something like this calculation: IF LEFT([State],1) = “A” Then “Yes” Else “No” END.
When I take that Calculation and put in on to Color it gives me all the Non-A States and A States on top of each other in the Panel Chart.
For example California has a is in Row 1 Column 1 but so is Alabama.
Is there a way to keep these States separate to a unique Row and Unique Column Number?
Thank you in advance for any insights!