Two Ways to Make Dynamic Slope Graphs in Tableau

Reveal hidden insights in line graphs by using slope graphs

Learn the benefit of slope graphs, a simple formula for making the end points of slope graphs automatically update based on the date selected, and how to use parameters to toggle between line graphs and slope graphs.

Hi. This is Ryan with Playfair Data TV and in this video I’m going to show you how to make dynamic slope graphs that will update based on the date range that you have selected. And I’m also going to show you an alternative way to make a slope graph that allows you to toggle back and forth between a line graph and a slope graph.

In case you’re not familiar with slope graphs, I’m going to first describe why they are needed, and why they are a handy alternative sometimes to a line graph.

So over here in Tableau Desktop, I’ve put together this basic line graph that looks at Profit Ratio by continuous month of Order Date, and it’s colored by Region. This is an okay chart. I’m a big fan of line graphs. But what gets lost in here is the individual change between the dimension members, or per dimension member, I should say.

So, for example, how high did the orange, or East region increase from the beginning to the end. Some of that gets lost in that middle kind of ‘spaghetti’ graphing that’s going on. So what a slope graph does, is it’s essentially a line graph that just includes the first data point and the last data point.

The easiest way to make a slope graph is to simply draw a box around all the marks that you don’t want, and hover over any of those marks, and click Exclude. And we’ve essentially got a slope graph. It’s truly that simple. Notice we’ve now got a filter that’s getting rid of all that spaghetti that was happening in the middle. Now it’s really easy to see things such as, the East region actually went from the lowest to the highest. That wasn’t as easy to see before, when we had all of those additional marks in the middle.

But the first thing I’m going to show you is a simple calculation that will dynamically keep the first data point and the last data point, regardless of the date range that you’ve selected. I’m going to undo once just to get us back to the traditional line graph.

I’m also going to replace my year of Order Date. I was filtering this previously to just one year in the sample data set. I’m going to replace that with just a full on, continuous, date range filter. I’ll leave it at All for now, and I will show that filter so that we can make some changes later on.

This is a calculated field. So let me start a calculated field and I will call this my Slope Graph End Points. And the formula is the function FIRST() equals zero or LAST() equals zero. Remember, any time you see blue in a calculated field dialog box, you can click on that function to get a definition of what it does, and the syntax for how it’s used or what the output might be.

FIRST(), it says, returns the number of rows from the current row to the first row in the partition. This is a table calculation. And what we have said in this first part of the statement, is we want the difference from the first data point and the very first row to be zero. So, essentially, we want it to be the first data point. That’s what that first statement says.

And then, LAST(), is the same thing for the last data point. We want the difference between the data point and the last data point to be zero. So, in other words, that is the last data point. And that is the whole formula.

So I’m going to click OK. And I’m going to drag that to the Filters Shelf. This is going to only keep the data points that are true. And let’s look at this one more time.

This is a Boolean formula. There’s only two outcomes. This whole thing is either true or false. That’s also why you’ll see a small icon next to this calculated field in the Measures area of the Data pane, that says T pipe F, True or False. That means it’s Boolean.

So I’m going to click OK, drag that to the Filters Shelf, click True. And if I click OK, we’re left with just the first data point and the last data point on our line graph. But what’s nice about this now, is if I change this date range to anything, so maybe I’ll drag this to February 2016, and let go.

Now, notice my first data point doesn’t start until February 2016. So, regardless of what I choose in this slider, I’ll also bring down the minimum range and make that February 2017. So we’ve got a year over year analysis going on. We can now limit the slope graph to showing February 2016 as the first data point, February 2017 as the last data point.

And obviously, just did this on the fly and there is a gigantic insight that we didn’t necessarily have visibility into before. When we broke this down year by year, it revealed a huge a decline in Profit Ratio for the Central region, year over year.

But now we’re going to make this even better. Line graphs are great. Slope graphs are great. I’m going to show you how to make a toggle that will allow you to do either-or. And we can actually unlock this ability for your end users to decide for themselves when they keep all of those additional data points on a line graph, or when they want the simplicity and better visibility that sometimes comes with a slope graph.

This involves two steps. It’s not too complicated. The first is to set up a parameter. So I’m going to click the arrow in the top right corner of the Dimensions area, and click Create Parameter. And I will call this my Slope Graph Toggle.

The data type for this one is going to be String, because I’m eventually going to allow my end user to choose from two words, Slope Graph or Line Graph, two phrases, I guess, technically. But they’re going to choose from two different chart types and these are words. The first is Line Graph, and the second is Slope Graph. That’s it for my parameter.

I now need to use the values in this parameter as part of a calculated field, to give Tableau instructions for what to do when each of these two choices is selected. So I’m going to click OK. I’m going to start a calculated field. And I’ll call this c. Slope Graph Toggle. The ‘c.’ is just telling me which of these is the calculation, which of these is the parameter.

And I’m going to cheat a little bit and copy and paste some of this in here. The name of our parameter was Slope Graph Toggle. If that turns purple, that means Tableau recognized it as a parameter in my data set. And then Tableau doesn’t like when you copy and paste quotation marks, so I’m just going to replace those with single tick marks.

So I didn’t want to have have you sit here and watch me type, but I am and I break this formula down for you a little bit on what it’s doing. I’m using CASE WHEN logic that references the Slope Graph Toggle. So I’ve got the word CASE and then the name of my parameter. That’s what comes first. Each WHEN statement then gives Tableau instructions for what to do with each of those allowable values.

Slope Graph we’ve already done so I’ll cover that first because that one will make sense. I’m just saying, when you select the Slope Graph, I want you to use that same formula that we did in my first example. The difference between the FIRST() and LAST() equals zero is what we were doing in that first example.

What’s new for the Line Graph selection, is we’re just saying, if they choose Line Graph, I want you to show every record that is not null. That’s what this NOT ISNULL() does. So this will just bring everything back. We would see every single data point, both the start of the line graph, the end of the line graph, as well as everything in between.

I’m going to click OK. I’m going to replace my Slope Graph End Points filter with our newly created Slope Graph Toggle calculated field. Notice this is still Boolean. There’s still only two outcomes. If it’s Line Graph, you either are null or you’re not. That’s the first Boolean statement. If the end user chooses Slope Graph, it’s again that difference between FIRST() and LAST() equals zero.

So I’m gong to exit off that, drop this right on top of my first filter where we had Slope Graph End Points before. Let go. I’m going to click True. Click OK. And we’ve got a line graph again. Reason it switched back to a line graph is that was the first allowable value in my parameter.

To unlock the ability for my end user to change the current value, there’s one last step, which is to show the parameter control. You can do that by right clicking on the parameter, and clicking Show Parameter Control. So again, there was line graph, now if I switch this to Slope Graph, those points in the middle go away. If I choose Line Graph, the points in the middle come back.

And again, this is dynamic based on the date range. So if I change the date range, the continuous axis expands. I’ve still got Line Graph selected, so I got a lot of spaghetti going on. But if I choose Slope Graph, we’ve now got this date range, but I’m only showing the start of the range and the end of the range.

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