Ryan shows you how to recreate the popular stock ticker gauges often seen on news stations. The explanatory dashboard element includes a conditionally formatted up or down triangle to reflect changes, the KPI value, and an enclosing Gantt mark.
Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you how to make a custom gauge that I call a stock ticker gauge. This was inspired by several media outlets that, when they’re showing how certain stocks are performing, they’ll show a little widget in the corner of the screen somewhere.
That typically is the value of the stock performance– so an up or down change– that’s preceded by an up or down triangle, which is also conditionally formatted based on whether the change is positive or negative. And then, typically, there’s some type of mark that encloses the whole gauge. That’s what I’m going to show you how to do– over here in Tableau Desktop.
To lay the foundation of this tutorial, we’re pretending that we’re in a business that’s looking at month over month sales. So I parameterized the selection here. And we’re currently looking at February sales versus January sales. To start this gauge, we’re going to use a tactic that is one of my little Swiss Army knives to unlock a lot of flexibility in Tableau.
If you’re ever wanting to just generate kind of a fake column or a fake row, I use what is called a Placeholder field. I use this so often that I typically make a calculated field out of it. And, typically, the formula is the aggregation MIN, M-I-N, 0. That just guarantees that value is always going to be zero.
However, you can also do these placeholder fields in the flow of your analysis. So to start this view, I’m just going to double-click on the Columns Shelf and type in MIN 0 and click Enter. And that just creates an x-axis for me.
So MIN 0 is just a placeholder. The number means nothing, but that number it always equals 0. Because it’s on the Columns Shelf, we just created kind of this fake x-axis. We’re just kind of starting the canvas. We’re giving ourself a place to work here.
The next thing I’m going to do is change the mark type from Automatic, which is a Bar, to Gantt mark. And you didn’t see much change, but that bar is now a dash at the top of where the last bar was. Because the value is zero, it’s just a single dash at the moment.
Gantt marks need to be sized by something in order to show up on a view. So I’m, once again, going to use a placeholder field. But instead of MIN zero, I’m going to size that mark by the number one.
This is another example of using Tableau in the flow. I can just double-click on the empty Marks Shelf and type in my second placeholder field, which equals the aggregation MIN one this time, instead of 0, and click Enter. You didn’t see anything change because, by default, that field or any field for that matter that you add to a Marks Shelf goes to the Detail Marks Card. That’s what that icon is telling me.
But if I just left-click on it and drag it to the Size Marks Card, my Gantt mark will now be sized by the number one. So it’s going from 0 to 1.
And what we’re doing is we’re laying the foundation for the right side of our stock ticker gauge. This is going to enclose the up and down triangle, as well as the positive or negative value on the stock ticker gauge. So there’s the foundation. Now that it is there, we can edit the axis to get this mark to whatever size we want. And you can do that by right-clicking on an axis and clicking Edit Axis.
And one of the options that you have is to fix the axis range. Let’s try out negative 10 to 10. And all I’m doing is I’m changing the range of my x-axis, which is widening it and it’s making the size of that Gantt mark smaller. If I close this and now that I’ve got a larger axis range, maybe I’ll hover near the right side of it.
And once that arrow cursor appears, I can left-click and drag this in, and I’ll just drag it as far as I need to get that Gantt mark to kind of look how I would like it to. I can also change the height.
So I feel like that’s a pretty good weight right there. But, obviously, I don’t necessarily want this centered like that. So another thing you can do, if you’re wanting to move the location of that stock ticker gauge and maybe make it right-aligned is you can edit fields in the flow. Instead of MIN 0, maybe I’ll bump that zero up to nine, which will move my Gantt mark towards the right side of this chart.
And you can do that by just double-clicking on a pill. It opens it up, and I’ll change the zero to a nine, the value of the Gantt mark moved up to nine. It’s then sized by one, which is pushing it all the way out to 10, and this is starting to look really nice. So we have a really good foundation here, may make that just a little bit shorter, and now we’re ready to add the values.
The first value is a pretty easy one. It’s just my percent change. So I’ve got something called Sales Change. I’ll throw that onto the Label Marks Card so that it shows up as part of my Gantt mark.
I’m not loving how it is aligned at the moment, so the next thing I’ll do is click Label and change the alignment to be left aligned. And now it shows up nicely next to my Gantt mark.
The last piece of this is to create a field that will show an up triangle when the period over period change was positive and/or a negative triangle when the period of a period change was negative. To do that, we’re going to start a new calculated field. We actually need two calculations if we want to color these independently of each other.
The first one is going to be my up triangle, and this calculated field has two outcomes. When there’s a positive change, I want to show an up triangle. When there’s a negative change, I want the outcome to be null. I want it to show nothing.
Whenever there’s only two outcomes, I like to use the Immediate IF statement. It’s the letters I-I-F, open parentheses, and then our criterion. So we’re going to say when Current Month Sales is greater than Comparison Sales, comma, and whatever I type next is what will be displayed when the period over period change is positive– so when Current Sales beat Comparison Sales.
I show this on another video. It’s called How to Conditionally Format Numbers in Tableau. But if you want to display an up triangle, you type an open tick mark because it is a string, and it uses a special what’s called Alt code character.
If I type Alt-3-0 and let go, we will see an up triangle appear. Then I close the string with another tick mark, comma, whatever I type next is what will be displayed when this criterion is false. So when Current Sales does not beat Comparison Sales, we’re just going to show a NULL value and close parentheses.
There is the entire formula. I will click OK. The second one will be a little bit easier because we’ve already gone through the logic of creating the up triangle. So I will duplicate that and edit the calculated field and change that to a down triangle.
And this time, we want a down triangle to show up if Current Sales is less than, so I need to change that operator if it is less than Comparison Sales. And I also need to change the up triangle to a down triangle. So this one would be Alt-3-1. That’s how you create a down triangle. So I just replaced the up with the down.
And the reason we are doing these as two separate calculated fields is in the final step, we will want to color positive changes one color, negative changes a different color. If we put these into one formula, the formatting would be all or nothing. So now that we’ve got both of these formulas,
I’m going to add them both to the Label Marks Card– so Down Triangle on Label and Up Triangle on Label. Because February versus January was a negative change, we only see the down triangle appear at the moment. If I bumped this up to March and it changed to a positive change, well, it just doesn’t have room quite yet. So let me jump in here and clean some stuff up, going to click Label, click this ellipsis, and I’m going to put the Down Triangle in front of my Sales Change and my Up Triangle in front of my Sales Change, click Apply. And now let’s see what happens if we bump that up.
Now we’re starting to see this come into place. So when there is a positive change, we see an up triangle and its value. When there is a negative change, we see a down triangle and its value.
Let me click on Label again and go clean some of this up. Looks like we have some extra spaces in here– just delete some stuff, click Apply. This is aligned a little bit better. Maybe I’ll type a space between my triangle fields and my Sales Change values.
And then, lastly– and this is the reason why I wanted to have these as separate calculated fields– I’ll make the Down Triangle maybe red and my Up Triangle blue so I can conditionally format these independent of each other. So now if I click Apply, click OK. When you see a down performance, you see a down red arrow. When you see a positive performance, you see an up blue arrow.
Last couple of things– typically, that Gantt mark is also colored by that positive or negative change. So I’m going to duplicate the Sales Change field and put it onto the Color Marks Card. You can do that by holding the Control key while you click on a pill and drag it where you like.
And it creates a copy of that pill. We see a color legend show up in the top-right corner. There’s only one value on the view right now.
So we’re just seeing one block of color, and there’s only one value in the color legend. Very important step is you need to edit these colors. I like to do that by just double-clicking on the color legend. You can also click on the Color Marks Card.
This will be a diverging palette. I’ll choose red-blue diverging. Whenever that change is either positive or negative, I typically change the steps to just be two. That will show one color for positive changes, one color for negative changes.
But here’s a little bit of a tricky part– there’s only one value on the view right now, so nothing would be diverging yet. We need to click Advanced and set the center so that it is always the number zero. That’s the trick to getting this to work. And now, if I click OK, we should see that blue color for positive changes.
But if we go back to negative changes, both the Gantt mark, as well as the colored triangle, turn red. Let me make that Gantt mark just a little bit shorter so that it’s more in line with the rest of the view, and let me make it a little bit skinnier as well. And we’ve got a nice looking stock ticker gauge coming together. From here, it’s all about the formatting.
This axis actually means nothing. So I could hide that by right-clicking on it and deselecting Show Header. There’s lots of extra lines in here.
You can get rid of those as well by right-clicking anywhere in the view and clicking format. There’s a tab for Lines. I might get rid of the grid lines, the zero lines– all of that is up to you.
There’s still a thin gray line on the x and y-axis. Those are called Axis Rulers. You can also turn those off. And I’m just cleaning this up more and more.
One last thing, there’s no title on this yet. This is sales. So I’ve got a little hack to get the name of the sheet or the stock ticker gauge lined up with the rest of the view. It’s another example of using Tableau in the flow. I’m going to double-click on the Rows Shelf this time and just type in the name of the measure that I’m using.
So the measure this time is Sales. So I open the string, type the words ‘Sales’, close the string, and click Enter. And that’s what is displayed on the Rows Shelf. We don’t need this header, obviously, so I will hide that.
You could go through some extra trouble. You could format this, make this a little bit bolder. Maybe make the font a little bit larger. But we’ve just created a nice custom gauge in Tableau that I call a stock ticker gauge. This was just the foundation to introduce the concept and this new type of visualization.
Obviously, I would want to use this with a series of measures. That’s an alternative to callout numbers. So you might see Sales, Profit Ratio, Quantity, as a stock ticker gauge along the top of my dashboards.
It’s a very explanatory metric just showing us at a 10,000-foot view, what are our KPIs? Are we going up or down. And then it it’s very engaging, gets the user to look at the dashboard, and see where they need to look next.
This has been Ryan with Playfair Data TV – thanks for watching!