Have you seen stock ticker gauges that show a full trend line that is colored by only the most recent performance? In this video, Ryan shows you how to do something similar with a single calculated field to add a nice (and effective!) aesthetic to your visualizations.
Calculated field that compares the current month’s Sales value to the previous month’s Sales value:
WINDOW_MAX(IF LAST() = 0 THEN SUM([Sales]) END)
WINDOW_MAX(IF LAST() = 1 THEN SUM([Sales]) END)
Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you how to color entire Tableau charts based on the most recent or current performance. By the end of this video, you’ll be able to make a chart similar to what you see on the screen here.
Notice that the entire sparkline from left to right is colored on the most recent month over month performance. If the most recent month over month performance is positive, the entire chart’s colored blue. If the most recent month over month performance was negative, the entire chart is colored red.
This was inspired by stock ticker gauges. If you follow the market, and you see a trend line for a certain stock symbol or index, that trend is typically over a single day in terms of seconds or minutes or hours. It could also be year to date. But in either of those cases, you typically see the entire trend, but then the entire chart is colored by the most recent performance, which I think provides a nice bold aesthetic, which is why I wanted to try to recreate this in Tableau.
I’ve shown you a different method for doing this in a blog post that required three level of detail calculations, but I found a much easier way to do this with table calculations using a single calculated field. That’s what I’m going to show you how to do here today.
So over in Tableau Desktop, first thing we need to do is make a chart to color. This could be created with any measure and any dimensions. Maybe I’ll go ahead and use sales, so I’ll double click on Sales. And we’ll use the same dimensional breakdown that you saw to start this video.
I’ll put Region on Rows. And I’ll convert this to a trend view by right-clicking on the Order Date field. And by the way, I’m using the Sample Superstore dataset. If I right-click on the Order Date field and drag it to Columns. I will choose Month continuous.
To mix this up a little bit, I did change the mark type to Bar. So in this case, we have spark bars instead of sparklines. If you’re following along, you probably would start with this, but let’s stick with bar just to get a slightly different aesthetic and look on this.
All right. With this particular tactic, I’m going to cut to the chase and show you the correct way to do it, but then we’re going to go a little bit more in depth. And I’m going to give you a little more detail on a couple of different types of table calculations. We’re going to talk about the WINDOW_MAX and LAST() functions.
But first to give you the formula, I’m going to start a calculated field. And I’ll call this my Chart Color. And this will be a Boolean calculation, but the root of it we need to isolate the current performance. And that’s where the first of these table calculations comes in. I’m going to say IF LAST() open parenthesis close parenthesis equals 0, then SUM of Sales.
And if you’ve never seen the LAST() function before– I forgot my END– you can click on it within a calculated field dialog. It’ll give you a short definition as well as the syntax of how it’s used. But LAST() equals 0 will look up the last value in the window. And again, table calculations by default go from left to right. So in this case, it’s going to look up the current or most recent month in my dataset. It’s going to return Sales.
One nuance of this function is LAST() counts as an aggregation. It doesn’t look like it’s being aggregated, but let’s say I removed SUM from Sales. I would get an error message. Tableau’s saying, we can’t mix aggregate and nonaggregates. It’s because LAST() is considered an aggregate. And in this case, I have not aggregated Sales. So I need that in there.
We also are going to need to apply that last sales value across the entire view. This is the part where I will go a little more in the weeds after I show you the correct way to do this. But for now, I’m just going to give you the correct formula, which would be WINDOW_MAX and then wrap that whole expression that we’ve already written. So that’s going to apply the sales value for the most recent performance across the entire chart.
In fact, I’ll click OK and just add that as a label for now so that you can see what I’m talking about. This is looking up the value of 18– I’m on the first row, Central region. 18,883 is being applied across the entire chart. And because table calculations, which I know this is a table calculation because it has a delta symbol on top of it, by default, because those go from left to right, we’re seeing the most current performance for every individual row from left to right.
So, so far, so good. I’m going to remove that because we’re not quite done. I’m going to go edit my Chart Color calculated field. And this is where it becomes a Boolean. I’m going to go down to the next line, type a greater than sign. And I’ll actually copy this first portion of it.
And all we need to do is change that 0 to a 1. This looks up the second to last value. So LAST(), if it’s 0, that’s the very last row. If it’s 1, it’s taking one row in front of the very last number. So that, in this case, is the second to last number.
So what we’re saying– because our date part is month, we’re saying look at the current month sales and determine if it is greater than last month’s sales. That’s what we’re doing so far. If I click OK, notice it’s now changed to a Boolean data type. And if I drag that to Color, we should have one color if the month over month performance was positive and one color– a different color if the month over month performance was negative. Maybe I will change these to blue and red and click OK.
So that is the trick. That’s the formula. I will share that in the related content below the video. That’s really all you need. But if you want to stay on, I’m going to show you a couple more reasons on why we had to use WINDOW_MAX to get that to go all the way across the view.
So let’s say that I made a calculated field and only made this Current Month’s Sales. And I’ll start with that same kind of root formula. IF LAST() equals 0, then SUM of Sales END. The current month sales looks good. But if I go to add that as another measure on the view, so that’s the second row for each region, you’ll see that I only have one single number.
And the problem with that is if I try to do the math– so let me duplicate Current Month’s Sales and call this Last Month’s Sales and just change the 0 to a 1. And I’ll put that on the view. We see one number for Current Month’s Sales, one number for Last Month’s Sales, but they’re not aligned. So I can’t do the math.
But when I go in here and add WINDOW_MAX it looks up that second to last month’s sales. I click Apply. You see that stretch all the way across the view. If I do the same thing for Current Month’s Sales, WINDOW_MAX, click OK, that one number is then extended all the way across the view.
And then to get our end result, what’s generating that color, is we can now do the math because there’s a number on every row. We can look at Current Month’s Sales across the entire view, Last Month’s Sales across the entire view, and then it just does that color to determine whether or not we outperformed or underperformed the last month. And most importantly, it rolls it out across the entire chart.
This has been Ryan with Playfair Data TV – Thanks for watching!