Online tableau training, tableau tips, & video tutorials

# Technical Features

Preview

Instructor

Ryan Sleeper

### Learn Syntax, Update Calculations, and Save Calculations for Future Use

Tableau provides several ways to update your views without leaving the “flow” of your analysis. See how to reverse engineer formulas on the view, update calculations, and save calculated fields for future use – all without leaving your flow of thought.

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you three different ways to use Tableau in the flow of your analysis. I’m going to show you how to reverse engineer a calculation that’s on the view so you can learn the syntax. I’m also going to show you how to modify that calculation right in the flow. So right within the Authoring interface on the Rows Shelf, I’m going to show you how to modify a calculation. I’m also going to show you how to save a modified calculation for future use.

To start with, I’m just going to build a quick Sales by Continuous Month of Order Date line graph over here in the Sample Superstore dataset. This is already a pretty good chart. Line graphs are my second favorite chart. But one of my favorite ways to take this a step further is to leverage table calculations to create a second row that computes the month over month difference.

If you wanted to do something similar, to start with, you would simply duplicate the Sales pill on the Rows Shelf. I will do that by holding down the Control key, clicking on that Sales pill, and dragging it right next to itself and letting go. On that second row, now that we have two Sales pills, we can modify these individually. So we can leave the first row as is.

But on the second row, I can add a table calculation to the measure that computes the month over month difference. And because the default addressing of a table calculation always goes from left to right, this will, by default, compute a month over month difference. If this feature is new to you, I encourage you to check out the video, An Introduction to Tableau Table Calculations, here at Playfair Data TV.

But at this point on the first row, we have a traditional line graph, sales over time. On the second row, we’re computing the month over month difference in sales over time by month. With this chart type, I typically like to change the mark type from Line to Bar. And let’s also color those bars by that same month over month difference. I will do that by holding down the Control key while I drag the Sales pill with the delta symbol over to the Color Marks Card.

So this was just laying the foundation. But it’s also an example of a chart type that I like to build in Tableau. The first way I’m going to show you how to do something in the flow of your analysis is we’re going to double-click on this Sales pill that has the delta symbol. And when you do that, it kind of gives you what I call an X-ray vision. So we’re looking at that underlying formula.

When we just hovered over Quick Table Calculation and we chose Difference, Tableau is doing some fairly advanced math for us behind the scenes. Some of this might be new to you. So my first tip on how to use Tableau in the flow is to reverse engineer calculations like this. Again, all I did was double-click on that pill. And now I can not only look under here to see what the formula was, but I could copy and paste that into a calculated field and learn from it.

Anytime you see something blue in a Tableau calculated field dialog box, it will look it up. If you click on it– so this ZN, for example, and it’s blue– if I click on it, it will look up that function over here on the right in this little data dictionary. It gives me a definition for what that function does as well as an example of how it is used in the syntax.

ZN, and it looks like, will– it says it returns an expression if it is not null. Otherwise, it returns a 0. Essentially, it plugs in 0’s. ZN actually stands for Zero Nulls. So we’re just looking down the dataset. If it’s null, it plugs in a 0. So there’s a number in every single row.

LOOKUP might also be new to you. You could click on that. There is a definition. I won’t read that to you word for word. But you can get an idea of what that does as well as how it is used in the syntax. So that’s the first way you can use Tableau in the flow. You can also modify those calculations, without even creating a new calculated field, and saving it. Let me give you an example.

If I double-click on that Sales pill containing the delta symbol again, I can not only look at the formula, which by default is saying, look in each cell– that’s what this first part does– if it is null, plug in a 0. Otherwise, show the value in that cell. Then subtract the sales amount. Offset– that’s what this LOOKUP is doing– by minus 1.

This is why the addressing of a table calculation becomes so important. When you’re going left to right, which is the default– so table across– that minus 1 is in terms of columns. So from left to right. If we were going top to bottom, the minus 1 would be in terms of rows. So right now, this LOOKUP is subtracting the value minus 1 column before the cell in question.

Minus 1 is the default. But here’s my second tip on how to use Tableau in the flow. You could change this to whatever you want. So if I change this to a minus 12 and clicked Enter, you’ll see the view change a little bit. Notice there’s now 12 nulls. It’s because the first 12 months in our data set don’t have anything to look up. Those are the first 12 months. So there’s nothing in front of it. So we had 12 nulls to begin with. And now what we are essentially computing is a year over year difference instead of a month over month difference.

I’m using this as an example because I laid the foundation with a table calculation. But this can be done with any formula. In fact, you can just double-click on here and type a formula if you wanted to. So if I double-click on the Rows Shelf and type SUM of Profit divided by SUM of Sales, I will create a third row that computes the profit ratio. We just made a new calculated field in the flow of our analysis. And again, if you double-click now that it is there, you get that X-ray vision to see it. You could save it if you wanted to. You could tweak it further if you wanted to.

Speaking of saving these, my third and final tip on how to use Tableau in the flow is to show you how to save these formulas for future use. I already showed you that you could create a calculated field and copy and paste the formula into that box. But I’m going to show you an even easier way. You can simply drag the new calculation straight over here to the Measures (Area). And when I let go, you’ll see that the formula is asking me to give it a name there.

Profit Ratio already comes with the sample dataset. So I’ll call this Profit Ratio Training Example. Click Enter. We now have a new calculated field that we can use, just like any other measure, for future use. I could start a new sheet, double-click on that Profit Ratio, and break it down by Continuous Month of Order Date if I wanted to, all by itself. There’s three ways to use Tableau in the flow.

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