How to Make Leapfrog Charts in Tableau

An alternative solution for showing rank and performance

Learn how to make ‘leapfrog’ charts, a dot plot variant that uses a Gantt chart on the dual axis to illustrate performance compared to average. You’ll also see how to use table calculations to display the rank for each mark.

Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you how to make a variation on a dot plot called leapfrog charts. Leapfrog charts are useful anytime you want to see how the performance of a specific dimension member is doing compared to the average for all the other dimension members, as well as what it would take for those specific dimension members to ‘leapfrog’ over another, if you will, which is where the name comes from.

So over here in Tableau Desktop, this starts with a dot plot on the left side. Can be made with any dimensions and measures that you want, but I will use the measure of Sales and break it down by Month of Order Date to see if we can see any type of trending as we move from the beginning of the year to the end of the year. So I’m going to start with discrete Month of Order Date on the Rows Shelf.

Yes, the Sample Superstore dataset comes with four years of data, so I’m just kind of pretending that there’s only one year of data in here. So you could think of all four years being aggregated into one, just trying to see how we’re doing by month. And next I’ll drop Sales onto the Columns Shelf.

I’ll make this fit Entire View so that we can see it a little bit better. And as I mentioned, the first part of this is created with a traditional dot plot. So I just need to change the mark type from Line to Circle. I’ll make those circles a little bit bigger so that we can see them.

And I’ll also add a little bit of detail to this. Again, this can be created with any fields you want, but let’s say that we wanted to see how we’re doing for specific regions. So I’m going to put the Region dimension onto the Detail Marks Card, which will get me four regions per month. So now I can start to look at these individual dimension members, see how they’re comparing to each other.

The next thing from here that I’d like to do is a tactic that I often implement, which is to highlight one of these specific dimension members. It’s accomplished by first creating a parameter containing the allowable values of the dimension that we are analyzing. So in this case, Region.

If I were wanting to do that, the easiest way to do it is to right-click directly on the dimension, hover over Create, and click Parameter. This creates a parameter with the proper data type and the allowable values already populated. Those allowable values will match the dimension members within that dimension.

So just a little bit of a shortcut for you, but take note of how this is spelled, the name of it. It’s called Region Parameter, because in the next step what we need to do is create a calculated field that highlights the dimension member that matches the allowable value in that Region Parameter. So I will call this my Region Highlighter. And the entire formula is the dimension called Region equals whatever that parameter was called, which is why I had you note that. It’s called Region Parameter.

And that’s the entire formula. Click OK. If I were to place that onto the Color Marks Card, the allowable value in this parameter, which is currently Central– because these go in alphabetical order, and the first allowable value is the first value there, so its Central– is colored one thing. Everything else is colored something else. I’m going to go ahead and recolor these a little bit, maybe give True some kind of turquoise highlight and False a more neutral color, like a gray.

All right, so there is the dot plot. Now, here’s where things get kind of fun and interesting and how this is kind of an innovative chart. What we’re going to do is create a Gantt chart on the right-hand side. That’s the first step. I want that Gantt mark to start at the performance of whatever I am highlighting.

So I’m highlighting the Central region currently. And I want one mark. To do that I’ll need a calculated field that says if the Region Highlighter equals the dimension member in the Region dimension, then show the Sales amount. You can write this formula in a couple of different ways, but whenever there’s only two outcomes– so in this case you either match the Region Highlighter or you don’t– then I like to use the Immediate IF statement.

So I’m going to make a calculated field. And it starts– well, I’ll call this Highlighted Region Sales. And it starts with the Immediate IF function, which is IIF, open parenthesis.

And we’re saying that if the Region dimension equals the Region Highlighter– oops, don’t need that extra IF in there. So Region dimension equals the Region Parameter, rather. When that is the case, I want it to show the Sales amount. When that is not the case, I want it to show a null value.

So this will just give me one mark. In this case, it will show me Sales for the Central region. Click OK. I’m going to place that onto the Columns Shelf so that it creates a second column.

We should see one mark. Yes, it’s highlighted, because we were only showing sales for the Central region. And what I’m going to do is navigate to the Marks Shelf for the right side only and make a couple of changes.

The right side is not going to be colored by Region Highlighter. I like to try to keep my Marks Shelves as clean as possible, because it can get kind of confusing on how those marks are being encoded. So I’m going to drag away anything that’s not needed.

So Region Highlighter is not needed at the moment. I’m going to drag that away off the view. Still have all the marks, they just aren’t colored.

The other big change I need to make at this point is to change the mark type from Circle to Gantt bar. That will get me one dash right where the performance of Central sales is. I’m going to call that good for the moment and combine this into a dual-axis combination chart so that both these layers are on top of each other.

There’s also a couple of ways to do this. The quickest way with the fewest amount of clicks is to hover over this second axis, and when that green triangle appears, click on it and drag it to the opposite side and let go. Both these axes should be on the exact same scale. So I will right-click on either one. And just to be sure they’re in sync, click Synchronize Axis. As you see, they were not in sync, but now they are, because we should see a dash going right through the middle of Central sales at the moment.

All right, another key component of this chart type is to show the average for each dimension member on your Rows Shelf. Again, this can be created with any dimensions. It doesn’t necessarily have to be related to time, but time is helpful because we can start to see some trends come together. But regardless of what dimension you’re using, the easiest way to add the average per dimension member is to right-click on an axis and click Add Reference Line.

And I’ll just stick with the default, which is Average Sum of Sales. But I’m going to get rid of the labels, because those become kind of messy. And the most important thing is I need to change the scope of this reference line. By default, it’s showing me one line across the entire– the average across this entire axis.

If I change that to Per Cell, I will see another dash, one for each of my 12 months in this case. So on the right side– actually, why don’t we color this so we can tell the difference? So on the red line, that is your average per dimension member. The gray line is the Gantt mark for whatever region you are currently highlighting. That’s their sales amount.

That reference line is going to be helpful to help us draw a line between this Gantt mark and that reference line to make sure that this is working, which is the next step. We need one more calculated field to connect these two lines. The calculated field will compute the window average minus the highlighted sales.

So one more calculated field. I’ll call this Average Minus Highlight. And this uses a table function called WINDOW_AVG. And then we’re looking at the Sales measure, so I need to put in SUM of Sales.

Whenever I’m typing formulas like this, I like to go ahead and put in the parentheses and make sure it’s valid at each step of the way, because it can get kind of confusing on where you close, or if you forget to close a parenthesis, you’ll get an error message. So I just like to try to clean it up as I go. So the first piece of that is correct. And we’re going to subtract Sum of the Highlighted Region Sales. And close that parenthesis.

And that looks good, so I’ll click OK. And it’s this newly created calculated field that we size those Gantt marks by. We’re currently on the Marks Shelf for the dot plot, so I first need to navigate to the Marks Shelf for the Gantt bars. And I’m going to drag Average Minus Highlight to the Size Marks Card.

And you might notice, we see some bars coming together, but they’re not quite lining up how I expected. Let’s navigate to this Marks Shelf and take a closer look at what’s happening. Note that Average Minus Highlight has a delta symbol on its pill, telling me that there is a table calculation taking place. And that table calculation is Window Average.

Well, by default, it’s going to compute that window average across the values in the table. But in this case, we need to do a couple of changes to change the addressing and how it is computing that difference between window average and sales for the highlighted region. We can edit a table calculation by clicking on a pill that has a delta symbol on it, and clicking Edit Table Calculation.

There is another video here at Playfair Data TV that goes more into depth on Tableau table calculations. But what we’re missing, just to be brief here, is we need to move to Specific Dimensions and include all of the relevant fields in the calculation. So we need to add Region.

And this is called calculation assistance. It’s trying to show me what it’s doing. That’s what these numbers are. It still doesn’t look quite right.

The issue now is we also need to restart that calculation every single line, so every single month in this case. And that’s what this dropdown allows you to do. So I click on Restarting Every Month of Order Date. It now looks like those averaged reference lines are, in fact, connecting with the performance for the highlighted region. So perfect, exactly what I wanted. Go ahead and close that.

The next thing I would like to do, just a little value add, is to color those Gantt marks by whether that difference between the average and the highlighted sales is positive or negative. We’ve already gone through the trouble of creating this calculated field, so I would highly recommend you use my very favorite shortcut in Tableau, which is to hold the Control key while you click on a pill. And we can just duplicate that pill and drag it to the Color Marks Card. So hold Control, drag that pill up to Color. And we should see those marks being colored by the same thing as they’re being sized by.

I’m going to make some edits to the coloring just by double-clicking on this color legend. With this one, I’m going to knock the steps down to two. This will give me one color for positive changes, one color for negative changes.

But I want you to take a close look at this. So let’s look at January. Central sales is right here, about 32,000. And Average Sales is right here at about 23,000, 24,000. Well, that’s a positive change, but it’s colored as if it were a negative change.

So we need to reverse these colors. And you can always click Apply to preview these changes. But what I’m looking for is a blue or something that indicates a positive change when there is a positive difference, red or something that indicates a negative change when there is a negative difference. Once I reverse that scale, it looks like it’s working for me. I’ll go ahead and click OK.

And I’m going to make a couple small formatting changes to this before I show you the last step. I didn’t mention this in the intro, forgot. But I’m also going to show you how to show the ranking for each of the dimension members on the view. So you can see what it would take for say Central region ranked number three to jump over whatever is ranked number two.

But before I do that, let’s do some format to these Gantt marks. First, in my opinion at the moment, they’re way too heavy in relation to the dot plot. So I’m going to click on the Size Marks Card and drag this over to the left, because this is really kind of a secondary insight. So you can make that pretty thin. That looks good enough.

Another thing you can do is move those Gantt marks behind the dot plot. You can do that by right-clicking on either axis. And depending on which one you click on, this will either say Move Marks to Back or Move Marks to Front. I will move the marks to back. That will make those Gantt marks go behind the dot plot.

All right, lastly I told you– oh, let’s do one more formatting thing. We no longer need this top axis. It’s duplicating what’s down here. So I’m going to right-click and just deselect Show Header.

All right, now last thing I told you I’d show you. What we’re going to do is add a label to each of the circles that tells us where each dimension member compared to the others per month. So these labels are going on to the dot plot. So I’m going to first navigate to the Marks Shelf for the circles.

And this involves another table calculation called Rank. You have the option to create a calculated field that computes the rank per row, but this is an example of using Tableau in the flow. Instead of creating a calculated field, I’m just going to double-click on the Marks Shelf and type out the formula, which is the table function Rank spelled R-A-N-K.

Open parenthesis, my aggregation, SUM, open parenthesis, my measure, Sales. And then I need to close the parenthesis, close the parenthesis, click Enter. So I just added a table calculation that computes the rank to the Marks Shelf in the flow of my analysis.

When you do that, by default, it goes to the Detail Marks Card. I know that because of this icon. It matches what’s on the Detail Marks Card. Well, I want to show these on the label, so I just need to drag this pill to the Label Marks Card. And you will see that icon change from the Detail Marks Card icon to the Label Marks Card icon.

We also see some labels appear. However, they do not look correct. We have four dimension members per row, and I’m seeing numbers like 11, 10, 9. So it’s not quite doing what I want.

This, again, has to do with the addressing of the table calculation. So we need to edit it by clicking into the pill and clicking Edit Table Calculation. And we need to include all of the relevant dimensions and fields here. And then we’re going to restart the ranking every month.

And those, just kind of eyeballing it, look correct. So I’ll go ahead and accept that. But I will make a couple of changes to the formatting.

I’ll click on Label. And I probably want to make those a little bit bolder font, maybe a little bit larger. And I like to personally make them white. Usually the hues below my labels are dark enough where I can have that reverse contrast. So I’ll make them all white.

I will center them. And some of them aren’t showing up, but– so this is optional. You do have the option to allow the labels to overlap. In this case, my circles are, in my opinion, far enough away from each other where it’s not causing too much of an issue.

This is going to be a case by case basis. If your circles are right on top of each other and you click this button, you may have some overlapping labels as well. But I’ll leave it at that for now.

That is a leapfrog chart. Just to show you how this works now, because we set this up to be parameterized, if we show the Region Parameter by right-clicking on it, and clicking Show Parameter Control, we can filter through different options. So if I move this to East, everything will be regenerated.

So my highlight moved to the East region. My difference between sales and the window average has been recomputed. And let’s try to analyze this, see what kind of insights we’re getting from this.

This is actually an interesting dimension member, because the East region started out– so January, February, and March, it was underperforming. It was in third or fourth place. You can see that it was significantly below the window average.

Starting in April, it started to make a big jump. And we’re now outperforming the window average. And by the time you get down to September, October, and especially November, it’s now actually widening its gap between its sales and the highlighted sales.

Just one example of how a leapfrog chart helped me do an analysis to figure out how a specific dimension member was performing.

This has been Ryan with Playfair Data TV – thanks for watching!