Variable Offset Label Bar Chart
For this blog post, let's use the actual data from my SNL viz. It's a simple list of people and the number of times they hosted the show. You can access the Google sheet here. Let's connect to it via Tableau and create a simple bar chart. It looks like this.
The problem here is with the labels...let's take a closer look:
You can see that many of the labels are cut off. We also see word-wrapping where it makes no sense to do so, see Candice Bergen where the e in Candice wraps to a second line. The reason for all of this is that there simply isn't enough space for the labels. We could stretch it to be taller, but we'd have names stretching across four lines and would still see the odd wrapping. Another solution to this may be to rotate the entire chart. This is what I often do, but sometimes you simply may not want to do that. (In my SNL viz, the rotated bar chart would have been far too tall and wouldn't be an optimal use of space).
My solution is what I refer to as the Variable Offset Label Bar Chart. So instead of labeling the bars all in the same "row", we offset the labels so that they have more space. In the end, the chart would look like this:
Okay, lets talk about how we build this. If you want to follow along, go to the "Problematic Bar - Sheet" sheet in this published viz on Tableau Public. In this sheet, we've built the original bar chart where we had all the issues with labels. To build this chart, we are going to create a dual axis representing negative numbers and this is where we will place the labels. When doing so, we need to make sure that every other label is "more negative" than the previous one. For example, we may place every odd label at -1 and every even label at -3.
So let's start with that calculation. The first piece of this is INDEX so that we can count each label from 1, 2, 3, 4, etc. From there, I'm going to use a little trick to determine if something is odd or even, by incorporating the Modulo function (represented with a % symbol). Modulo simply returns the remainder of a division problem. So 5%2 is like saying, what is the remainder when you divide 5 by 2. In this case, the remainder is 1. So in order to determine if these labels are odd or even, we can simply take INDEX() % 2. Even numbers will yield a remainder of 0 and odd numbers a remainder of 1.
Now that we have that under our belt, I'm going to start off simple. I named my calculation "Label Position" and used the following calculation:
IF INDEX()%2 = 1 THEN -1 ELSE -3 END
Basically this says, when the label is in an odd position, place it at -1 and when it's even, place it at -3. (Note, this is not what we will use in the end, but it will be our starting point). So let's take this and add it to Columns, dual axis, synchronize it. Oh...and get rid of that pesky Measure Names field that always gets added to the Color card against my wishes.
We will then do some cleanup by making the negative bar very thin and change it's color, hide the Name header, and remove the current labels.
Now, all we have left to do is to label those thin bars. We could just add the "Name" field, but I want to make sure that it shows the person's first name on the top line and the person's last name on the bottom line. To do this, I've created a calculation called "Name - Two Rows" that will split these. Also note that I am using a little trick where I open a quotation followed by a carriage return then close it. This will, in fact, do the same thing in your results.
TRIM( SPLIT( [Name], " ", 1 ) ) + '
' +
TRIM( SPLIT( [Name], " ", 2 ) )
Now let's add this field to label on the thin bars. You'll see that the negative bars change length. The reason for that is the Lable Position field, which contains the INDEX table calculation, is now computing incorrectly. So we will set this to Specific Dimensions and check all boxes. From here, we will adjust the labels to be at the top (the top of that negative bar is at the bottom of our screen) and centered, and make sure labels always show:
I like to do a bit more cleanup by hiding the axes, getting rid of any grid lines, and adding a white border to the thin bars to make them even thinner. The end result looks like the following:
How much better is this than our original?
But we do have a bit of a problem. Our Label Position calculation hard codes in positions of -1 and -3. What happens if our data changes (which it all does)? What if Alec Baldwin hosts every episode for the next five years. That would bring him from 17 to 117. How would this impact our chart? Well, it would look like this:
We are back to labels running into all the other labels. Positions of -1 and -3 worked fine when the longest bar measured 17, but when it's 117, -1 and -3 are far too small. The best solution to this is to simply use a ratio of the max value. So in our Label Position calculation, we will replace:
IF INDEX()%2 = 1 THEN -1 ELSE -3 END
with
IF INDEX()%2 = 1 THEN WINDOW_MAX(SUM([Hosted]))/-20 ELSE WINDOW_MAX(SUM([Hosted]))/-7 END
This calculation takes the Window_Max (which would be 117 in the example above) and create ratios. I chose to divide the Window Max by -20 and -7. Why these numbers? Well, they just worked. You can adjust them how you like or even parameterized them.
So when we make this change, our chart looks great with the 117 number or the 17 number (see both of them below).
That's pretty much it...although, I'd love for this thin bar to be a dotted line (like in the header image of this blog post). To do this, we will use Measure Values to create a "rounded bar chart". I'm not going to write down every step of this, but to understand this technique, check out this blog post from Lindsay Betzendahl on how to do it. I'll also show you how to do it in the GIF below. The key is that ultimately, a rounded bar chart is just a line connecting the value of 0 to our Label Position. We will then make that line very thin and change it to a dotted line (which just recently became available in Tableau). Check out the gif below:
The result looks something like this:
Alright, that's it! Again, you can reference how to do this in this viz published on Tableau Public. (Oh, and this is my 200th viz on TAbleau Public as well 😊).
I hope that you find this technique useful. Thanks for reading!
───────────────────────
Need help with anything related to Tableau? Through Moxy Analytics, Ken and I provide consulting services such as Tableau Lifeline (get us for 1 hour to help solve a sticky problem), Fractional Data Hero (get us on your team for N number of hours a month for whatever you want us for), Tableau & Dataviz Training, and of course, project work. Click the Icon below if you are interested.
No comments: