## March 22, 2016

# Tableau Tip Tuesday: How to Create Small Multiple Line Charts

For this week’s Tableau Tip Tuesday, I show you how I created the small multiples line chart of the race for the Republican presidential nomination. These charts are also known as panel charts. These can be quite tricky to make as sometimes you have to play around with the scope of your table calculation to get it quite right. This video should certainly get you started in the right direction.

Here are the two calculations you need to create the column and row dividers:

** Column Divider**

(index()-1)%(round(sqrt(size())))

** Row Divider**

int( (index()-1)/(round(sqrt(size()))))

Subscribe to:
Post Comments
(
Atom
)

Awesome stuff! When I follow this step-by-step, I am getting multiple dimensions per panel. I'm using Tableau 9.2. I've got everything computing by the dimension in the marks card. Not sure where I'm going wrong...I've also triple checked the column and row divider formulas.

ReplyDeleteAny ideas?

If I had to guess, you have nulls in your data set, which is what would cause that problem.

DeleteI did have nulls, but I cleared those out and still no luck. I created some fake data too and no luck.

DeleteIt works when I have the continuous date set to month, qtr, or year. But anything less than month, it puts all of my lines in one box. I really need it to be set on exact date/continuous for it to look right. Thanks for reading!

Zach, can you post a link to a workbook with the problem?

DeleteTry this link: https://www.dropbox.com/s/2dt016ur0mgblzv/Book1.twbx?dl=0

DeleteIt's fake data, but same problem persists.

Zach, one thing I noticed about your data set. Some Qcodes have data on a day, that other Qcodes do not. I think these gaps would create a similar problem to having nulls in the data for those dates.

DeleteThese differences exist at both the week and day level, but once you get up to the month aggregation level, all Qcodes have month level data, so that's when the divider calcs begin working.

If your original data source shares these same characteristics, then that's probably the source of the issue.

Interesting! Thanks for the reply Matt. So you're saying that basically all entries need to have the same date-ranges? That's definitely a problem with my original data source -- some values end in December 2015 while others may carry thru January 2016. I can see that being problematic now.

DeleteI should be able to join to a Time_Date_Dim table then and basically assign a "0" value where the date isn't available?

Since I'm still dissecting how the calcs work, I can't say for sure, but I'm pretty sure your solution will work.

DeleteIf there are at least nulls existing for every date/review/qcode combination in your original data source, you could use the ZN function in tableau to replicate your Reviews measure via a calc field and transform the nulls to zero's instead of filtering them out. That would save you the join work.

In trying to understand what's going on under the hood, I recreated the formulas in Excel and based on my generated Column/Row numbers, did a basic quadrant chart showing where the value for that data/date combination would go. Then I made a tableau workbook based on that data set and sure enough, the calcs basically break the plot because different Qcodes are plotting into different quadrants.

I don't know exactly why your data causes the calcs to break in the exact way seen in your sample workbook, so I'm hoping Andy will chime in and let us know if I'm missing anything :)

Here's a link to a folder with my example tableau file and excel workbook: https://www.dropbox.com/sh/n7sk6u6qqjmus9c/AAAtSAihBGhWXbQ283-8SRloa?dl=0

Hope this helps!

Thanks for the thorough review. I did go back to my source data and applied ZN to my formulas to force nulls to 0 and still no luck. The weird thing is that it appears to work when the date range is discrete (meaning it breaks the Qcodes up into it's 4 respective quadrants). But the discrete dates are clearly counterproductive to what the view is supposed to show.

DeleteI'll keep giving it the ol' college try. Thanks Matt!

Great post! I successfully combined this with the KPI Donut chart post to show building occupancy levels on our campus within one view. Any resources out there to help me understand what's going on with the row/column formulas? I enjoy learning the nitty gritty when I can.

ReplyDeleteWhich parts need explaining?

DeleteI'm assuming that tableau is drawing the data into the different partitions based on those calcs, but I'm not sure exactly how those formulas helping tableau determine that.

DeleteI joined in to help Zach in the thread above which lead me to some thought exercises about the formulas so I think I sorta get it now. Would still love to know if I'm missing anything.

DeleteAwesome! I always wondered how to make these small multiples matrices in Tableau. Now I know - thanks so much for sharing!

ReplyDeleteHi Andy!

ReplyDeleteGreat Tableau Tip!

Simo and I replicated it using Sample Superstore building panes of sales by subcategory by quarter, and stumbled upon some of the issues the other guys here already raised.

As "Copiers" subcategory didn't sell in the first quarter of the dataset (Q1 2011) the calculations tried to fix it just using Q1 2011 of the following subcategory (causing a translation of all the subsequent values for subcategories in Q1 2011).

To solve this, we changed the "quarter" tableau date in a string (str((YEAR([Order Date])))+"-"+str(DATEPART('quarter', [Order Date]))), and this works.

We guess this has to do with densification, and the tendency of Tableau to fill gaps in data when working with table calcs.

We thought this could have been an interesting use case to add in this space.

See you soon,

Simo and Nicco

This post is fantastic. I'm having a bit of trouble giving both the end points a value as well as showing the title at the top of the pane. I've set marks to pain to get the title, but ideally I'd set line ends to 'value' and bring in the title another way (replicating 'Cruz' or 'Bush' at the upper center of the pane).

ReplyDeletehi -- this is very helpful. I am 90% there but I can't get the labels (ie like your candidate names) to appear above each graph. What's the trick to that?

ReplyDeletethanks, noah

It's simply a constant line on the secondary axis. Download the workbook and you'll see how I did it.

DeleteHi Andy! Thanks a lot for this, its quite incredible and super useful!

ReplyDeleteI was wondering if you could help me out with a problem I have with the labels. I sometimes see two labels (with different values) or sometimes non at all. I was wondering if you had any idea where this could come from and how to fix it?

Thanks, Tom

Yeah, that's the tricky bit. It's likely the way you have your table calc set up.

DeleteHi Andy - I create a similar view and am trying to add the label now. You mentioned to look at the Max Rating Offset calculation that's on the secondary axis, then go to that field on the marks card and you'll see how I set the label up. Where can I get the workbook to look at this calc? Happy to send you my workbook too. Thank you, Colin

ReplyDeleteThe workbook is embedded in this post. Click the download button.

DeleteRight, I download but that doesn't provide the calc for adding a label header, from what I can see? Sorry to be a pain.

ReplyDeleteP.S. Have watched many of your videos. Tremendous help to me! Much appreciated.

Sure it does. It's on the secondary axis.

DeleteThanks, new to Tableau. Found it, but not intuitive to me on what to do to create labels on panels. Is there a step-by-step doc or video?

ReplyDeleteHi Andy,

DeleteJust want to say huge thanks for your tutorial - it really helped me and I sailed through putting together a similar viz. Like some of the others noted, I had some difficulties working out how you got the individual headings to work, but downloading the workbook and some perseverance paid off. Thank you so much!

PS - here's what I made ... https://public.tableau.com/profile/rmchapple#!/vizhome/DomesticAbuseNI/DomesticAbuseNI

Way to persevere Robert! Well done!

Deletehow do you add the labels to show up at the middle of each box at the top. also they appear to be live and interactive.

ReplyDeleteIt's done with a dual axis. Download the workbook and see for yourself.

DeleteSo i did everything exactly in the workbook. my struggle is with the constant line. how do you assign the label field to the constant line. It only seems to accept available choices like "field label" or "field values" etc.

Deletealso when i try to right click the candidate label it does not seem to appear like a constant line that can be edited. I am missing something. It could be really simple but I am little frustrated. tried for an hour yesterday :-). Can you please help.

Hi Andy,

ReplyDeleteDo you have a tutorial for adding labels to the panel charts?

All of my tips can be found on this page - http://www.vizwiz.com/p/tips.html

DeleteAs for that specific tip, download one of the workbooks and see how I did it.

How do we assign the candidate name on top of the each chart?

DeleteYou can download the workbook to see how I did it.

DeleteDear Mr. Kriebel - thank you so much for tremendous help with this and other tricky questions! One more question - is there a way to turn trellis chart into a filter? For example, each panel in my trellis chart corresponds to a market with 2 lines: one is expenses for that market, and the other is the average for all Phase 1 markets. I would like to be able to click on a panel (market), and it should take me to another dashboard with more details on that particular market. Is it possible at all?

ReplyDeleteSure, just use an action filter that has the target as the other dashboard.

Delete