CASE / WHEN, IF / THEN, IIF Logic in Tableau

Learn the foundation of logical statements that can be transferred over multiple tools

In this tutorial you will learn how to implement three different logical functions within Tableau Desktop. You will also see how this logic can span across other tools and software with a brief introduction to logical functions in R and Tableau Prep as well.

Hi, everyone this is Ethan with Playfair+, and in today’s video I’m going to be walking you through different logical functions within Tableau. We’re going to cover Immediate IF statements, IF ELSE statements, as well as CASE statements in today’s video. So let me jump right in, and to kind of set the stage, what I’m going to be showing you is how we can apply these logical statements or use these logical functions to create certain scenarios. So, in today’s demonstration I’m going to be walking you through and showing you how to apply certain percentage increase in sales based on the category dimension member.

So, let’s jump right in, and the first example I’m going to show you is the immediate if statement. So let me open up a new calculation here, and we’re going to say IIF, this is our immediate if function. And you can see here in Tableau, when I type in the function it actually gives me this helpful little hint here that says what we’re trying to do is it’s essentially the syntax, so it tells us that we have our test conditions, comma, then this is going to be value one, comma, value two. And then it also gives us kind of a else, if nothing else than this value. So, what does that mean? In the first statement here, after the parenthesis, that’s our test condition. So, what we’re going to be looking at is for this particular example, if the category equals what we specify, then, so comma, then it goes to value one. If the statement does not meet that condition, then it’s going to go to comma, then value two, which would be this else statement here within our helpful hint or tool tip. So, what I’m going to do is say category, and we can hard code a category in here if we wanted to as our condition. So, if category equals technology, for instance, then we can do comma. So, this is our condition statement, category equals technology, comma, then our value that it’s going to send is sales.

And we’re going to say for this particular thing, we’re going to want to look at the increase of sales for technology if sales increased by 5%. So, we’re just going to do 1.05, it’s going to multiply our sales by itself, and then increase it by 5%. And again, that’s only if category equals technology. So, I’m going to do a comma here, and I’ll just put sales as our value two. So, what does this say? If I were to read this like a sentence, it would say, if our category dimension equals technology, take sales and multiply it by 5%, if it is not equal to technology, then just give me back the sales information. I’m going to call this our IIF, we’ll say, IIF logical function, and I’ll click OK. Now let’s apply that here on the canvas or in the view and see what we got. So, I’m going to add category to rows, and I’ll just add sales to text. So, this is our base sales figures for each category. And then I’m just going to bring that IIF logical function to text as well. So now what we can see is furniture stayed the same, office applied stayed the same, but technology increased by 5%. And that’s all driven off this logical function in this conditional formatting. So again, I’ll zoom in here actually this time, let me zoom into this. So, you guys can see it a little better. And just to explain it one more time, this is our immediate if statement and we have our conditional, our condition here. So, condition one category equals technology.

If that condition is true, it’s going to give me sales times 5%. If this is false, it will just give me the sales. So just to demonstrate this live, if we look here, and I’m actually going to increase the size here a little bit so you guys can see a little better. All right. So now we can see here if I change this category to say office supplies, office supplies, and I click apply, what we’re going to see is the view update in real time where technology will flip, it’s back to being the same, and now office supplies is actually getting a multiplier of 5%. Now we can also take this a step further. So, this is another tactic we can apply. We can actually allow the user to choose which category dimension member that they want to apply this particular logical function to. So, using Tableau’s parameters, which I’ve pre-built here, and I’ll just preview this, I’ve built a parameter that is a data type of string, and it has these allowable values here. These are just our three category dimension members.

So now what will happen if I plug in what if category into our conditional statement, if I click okay, and let me just show this parameter on the view. I’ll drag it over here. But now what we can do is allow the user, the user to choose which category they want to apply that 5% increase on sales to. So right now, it’s applied to technology. The user can then switch it and apply it to office supplies. They can apply it to furniture and so on and so forth. Now this is great, but what if our executive team came to us and said, you know, we want to apply a 10% increase to office or to furniture, a 3% increase to office supplies, and a 5% increase to technology. With an immediate if statement, we only get one conditional statement here and then a value one and a value two for our return.

The next approach that I’ll show you is applying this tactic using an if else statement. If else is another logical function within Tableau. So, what I’ll do is create a new calculated field and we will call this our if else logical function. And then we’ll call this our if and if is our function. And what we’ll do is we’ll say if category equals technology, then sales increase by 5%. And within that category, we’ll say if sales increase by 5% within the syntax within Tableau, we have to end the statement. So, if category equals technology, then sales times 5% increase. Now let me just leave it right there for now. I’m going to click OK and let me add this to the view as well. So, I’m just going to put this in text as well. Let me expand that a little more. And now we can see what’s happening here is I have our what if and actually let me go ahead and put this to technology as well. We have sales which is our top number, our immediate if logical statement which is our second number. And then we have our new if statement. And that if statement, if we recall, and let me just pull it up here and let me zoom in this time. If you guys recall, what’s happening here is I have my if function, our conditional or our condition one, and then it’s going to return value one. So, sales times 5%. But I leave it at that.

So, if what this is saying is in our data, if this condition is not met, then it’s just giving it a null value within the data. And we can actually see that happening right here in our table where I have furniture and I have a value for, if you can see it in our tooltip, I have a value for our immediate if function and I have a value for our sales function, but I don’t have a value right now. It’s null for our if function. And that’s because I haven’t declared any other condition here. Now what I could do is say else sales and when I apply this, you’re going to see that value fill in. So now this is equivalent to our if else statement. We have our condition. It’s returning value one. If that condition is not met, it’s going to return a value two. But the difference between an immediate if or I should say one of the differences between an immediate if and an if function is the ability to encode additional conditions within the function itself. So, if I jump back to Tableau, what that means is if I say if else or excuse me, else if, else if category equals office supplies, then sales times, and I think I said earlier, 3%. So, if category equals office supplies, then sales times 3%. And if I apply this, now you’re going to see that logical function being applied to two different categories, category dimension numbers. We have our office supplies now being multiplied by 3% here and I have my technology being multiplied by 5% here. And we can do that as many times as we’d like. So, I could do else if category equals furniture. Furniture. And then we can do that as many times as we’d like. Then sales times 10%. And I can apply that. And we can see that happening here.

So that’s also a benefit of the if statements. You can encode multiple conditions and return multiple values if those conditions are met. And then at the end of the statement, we kind of close it with else and then kind of a finalized condition. So, if anything else, if none of the conditions have been met at the end of our if statement, that else is kind of a catch all. Where it’s going to apply whatever logic or value you declare there, it’s going to apply that to essentially the rest of the data. This is a great way to encode things with if else statements, but there’s also one more logical function that we can use within Tableau called a case statement. So let me demonstrate that. It’s essentially the same thing, but with different syntax. So case is what it’s called. That’s the function. And you can find that in the Tableau data dictionary here. All of these functions you can, if you’re ever curious, you can click the gray arrow here. It will expand this menu and you can actually get kind of a detailed explanation of that particular function. So case, you can see some of the syntax here, but really, it’s very similar to the if statement. So, what we’re going to say is case and the first thing we do on a case statement is declare the dimension member that we’re interested in applying logic to. So, we’ll say case category and then I’m going to say when category and it references back to the top, so I don’t have to duplicate that here. When category is technology, then sales times 5% when office supplies, then sales times 3% and you can probably figure out where I’m going with this. Furniture, then sales times 10%. And just like with an if statement, you can see that the value statement, we can close this with else sales. So just in case, for instance, if the business added another category here, this is kind of our catch all where it’s not going to just apply a null value. It’s going to apply the sales measure as the value that it’s going to return if none of these conditions are met. And then just like with if as well, we have to close it in Tableau with this end function. So, this is our case logical function.

Click OK and let me add that to our view as well. So, we can see all four of those figures now and let me make this a little cleaner so we can all know which values are which. Just going to turn this into a small table and let me expand this out and increase the font size so you can see it. So now we have, let me reorder this, our sales figure here. So, this is just our sales measure directly from the data. We have our case statement logic here. We have our if else statement or logic here and we have our immediate if logic here. So, this has been a quick kind of crash course on logical functions within Tableau. But I do want to stress that these functions can also be found in many other tools. R, Python, SQL, Tableau Prep, Alteryx, all the other tools that we use in our day-to-day within data. We can take the same logical functions, maybe slightly different syntax depending on the tool, but the idea is still the same. So, if you can understand how these conditions and you’re encoding these conditions and how you’re returning the values, if you can understand that logically within these logical functions, you can apply that on different tools.

So as an example, I’ve kind of built two other examples here. One in Tableau Prep where I’ve essentially taken the case statement logic and applied it here. And you can see obviously Tableau Prep is going to use the exact same syntax as Tableau desktop. So, this is almost a, well essentially it is a copy and paste over and this should be sales actually. See, it wasn’t copy and paste. Sales, but this is essentially the exact same calculation sales, but this is essentially the exact same calculated field that we had just gone through in Tableau desktop applied here in Tableau Prep. And we can see if I save that it’s going to add it to our data source before we even get it in our tool. We’re declaring kind of this calculated field up front in our data prep step. We can also see that being applied in other languages here as well. So, this is an example of it being applied in R. So, you can see here I’ve imported our sample superstore data into R. I’m using Dipler to apply this case when logic and it’s essentially you can see the exact same syntax here being applied. So, when category equals technology, then I’m taking my sales measure and I’m taking category equals technology. Then I’m taking my sales measure and I’m multiplying it by 5%. And then here I can change these percentages just like I did in Tableau desktop. And if I rerun this syntax here, what it will do is add another variable to our data set. So, if I view the data, I can scroll over here. I can now see this what if sales figure and I can see for the different categories it’s being that different logic is being applied here on the row level.

So, this has been a crash course in logical functions within Tableau. But I also want to stress if you can understand the logical the syntax that’s being applied and how those conditions are met and those values are returned. If you can understand that logically within Tableau, you can take that same mindset and apply that different syntax over to other tools and essentially apply those same logical functions there as well. This has been Ethan with Playfair+. Thanks for watching.