Tableau: Using Dates AND Dimensions on the X-Axis.
In a nutshell, I often want to re-use a chart with different Dimensions and Measures, allowing the user to select what to display on either axis, and how that should be sorted. Sometimes, “sheet swapping” is the way to go, but I dislike most kinds of repetition, and creating multiple versions of one chart falls under that heading. However, often the switch for an axis will be between a date field and a categorical Dimension, giving an additional layer of complexity, where dates should always be chronological, but perhaps the categorical fields should be sorted in descending order by the selected Measure.
Note, in the image above, that the date format changes when switching between months and quarters, and that the sort order is chronological, or in descending order of the Measure for non-dates. This can all be done with just 3 calculations and two parameters. I’m only using a few states in this example, but since profit can be negative, potentially making my axis labels far from the bar, I’ve also added in two options to make reading from the axis easier. This requires one more calculation. All in all, that doesn’t seem too bad, to me. The option is always there to extend the functionality to calculated colour and/or label fields.
The two required parameters are straight-forward — two strings: a choice between Sales and Profit, and another between Month, Quarter and State. Two calculated fields are required for the X-axis. The first, which will remain hidden in the chart, governs the sort order of any of the date fields in your parameter. The trick here is that any non-date fields have a value of NULL, which means that control of the sort order will effectively be passed to our second field.
Ideally, I would separate out the two calculations for the order month and quarter, so that those values could be cached, and then use those values instead of the DATETRUNC calculations here. Anything that contains a parameter cannot be cached by Tableau, so separating things out can help performance. For the sake of simplicity here, I’ve done it all in one.
The second field is what will be displayed as the X-axis label. As much as I hate converting dates to strings (I never understand why people have string representations of dates in their data), in this case it’s unavoidable, as the output of a calculation must be consistent (you can’t mix dates and strings), and we’ve got the State field to think about in our second calculation.
The calculation below uses DATENAME to convert parts of the date field to a string, resulting in MMM-YY format when Months is selected, and YYYY-QQ when Quarter is selected. For the State, as it’s already a string, no transformation is required.
A quick switch for the Measure we want to display, and that’s it all done. Remember, if possible, switching between aggregated Measures (SUM([Sales]) etc.) can give a big performance boost to your dashboard. Using just [Sales] means that every underlying row of data must be checked (just to see if a parameter equals a value, nothing to do with the [Sales] field, so it really doesn’t need to happen), whereas switching between aggregates is one single comparison.
Using the fields above, we can now plot a fairly standard bar chart (but only one of them, with all the complicated formatting you desire). If, like me, you have stakeholders for whom you’re making this report, you’ll know that whatever you do isn’t going to be quite what they want (multiple times, quite possibly ending up back where you started) but now, at least, I only have one chart to make all of those little changes to. Hurrah.
However (there’s always at least one however), I notice in my full dataset that the Profit values can be quite negative, causing the default axis labels to be quite far from their values. To make it easier to read the value for each state, there are (at least) two options: alternating bands of colour for each state; or moving the axis labels to the zero Y-value. Creating bands of colour is one of the myriad “MIN(0)” hacks out there. I don’t want alternating bands when the months or quarters are displayed, and there are no controls to determine when shading should be displayed, so I use Reference Lines (lines, not bands, because my X-axis is discrete — in this scenario it makes things much easier). So, one more calculation:
When the choice is State, if we are looking at the 2nd, 4th, 6th… item along the X-axis, then give me a value of zero, otherwise give me NULL. This value can be used to create a reference line. A couple of often overlooked options at the bottom of the Reference Line dialog — Fill Above and Fill Below — can both be populated with a colour, effecting a shaded bar on every other X-axis value.
This will give you a chart something like this:
The second option is to move our X-axis labels closer to the zero line. This will cause the labels to overlap the negative bars, so judicious use of colour is advised. This solution also uses a MIN(0) hack, but this time we plot it as a series. Right-click in the Columns shelf and select New Calculation, then type MIN(0). This gives us a new set of values to attach Labels to, just where we want them.
Converting the Mark type to Gantt, dragging the [Sales or Profit] field to the Label and then clicking on the Label button to set the Alignment to be Bottom Centre almost gets us there, but I don’t like that the labels now touch the X-axis, I want a bit of space. Sadly, normal space doesn’t act how we want (try adding a trailing space to your label), so we need to do a bit of physics and enter the realms of alternative space. Two spaces, in fact. I’d show them to you, but that’s kind of problematic, because I don’t think Medium recognises them, and they’re not much to look at. The first is a non-breaking space which, when placed next to something, does not allow a line break (that’s my simplified interpretation, anyway). You can copy the NBSP character from this Wikipedia page, and paste it into your Label. The second type of space is just another UTF-8 character that puts some space in, but isn’t the same as a normal space you’d type, so it gets treated with respect and is allocated, well, space. You can copy and paste that one from this page.
Using these characters, we can force some space to appear after the label. But, because the number of values on the X-axis may decrease, causing the labels to switch back to horizontal, this would cause them to be slightly off-centre (enough to bug me, I’m sure most people wouldn’t notice). If we use even the alternative space before the label, it will cause a line break, so what I’ve ended up with is a label value book-ended by the NBSP and the second space: NBSP+[space]+[Label]+[space]+NBSP. An incredibly helpful illustration image:
A quick aside — why not just place the labels at MIN(-100)? If you’re confident that your Y-axis range is relatively static, this is a perfectly reasonable option, but if you have large variations, then your labels will appear closer or further from the X-axis depending on what you have filtered.
What this gives me is a chart with labels that are slightly below the X-axis, but which are also still centred if the chart has enough space to display the values horizontally. One of the downsides of this method is that there will always be negative values on the y-axis, even when displaying purely positive values.
If you’d like to download the simple workbook containing these two outputs, you can find it on Tableau Public.
