Sign in or start a free trial to avail of this feature.
1. Logical Functions in Tableau
Logical functions are incredibly useful for building custom categorizations in Tableau. In this lesson, we learn how to use IF/ELSEIF functionality to allocate each customer a Small / Medium / Large designation.
To explore more Kubicle data literacy subjects, please refer to our full library.
- Unlike Excel, Tableau has the ability to easily create multiple branches to an IF function
- The functionality works as follows:
--- IF is used to create the first condition
--- THEN is used to return the output for each branch
--- ELSEIF is used on each subsequent branch after IF
--- ELSE is capture all remaining cases outside the stated conditions
--- END finishes the function
If you have used Excel before, you should be familiar with the logical functions, such as if, else, and, and or. These functions test certain conditions and then return a value based on the results of that test. Let's start with a simple example. I want to create a variable that categorizes my products by size. If the sum of sales is less than 20,000, then the product will be considered small. If sales are less than 40,000, the product will be considered medium sized. Otherwise the product will be considered large. And to do this, we're going to use IF and ELSEIF. Let's right click and create a calculated field called Product size.
And I'm going to start by writing IF.
And when I write IF, I need to then include a condition or an expression and I'm going to check if the SUM of sales is less than 20,000.
And if it is, then I'm going to return Small.
Then ELSEIF the SUM of sales is less than 40,000, I'll return Medium.
Otherwise, I'll return Large.
And then I'll write END to finish my statement.
As you can see, this function is pretty easy to create. We write IF and then include our condition. And then write THEN and include our answer. If we have numerous conditions to check, we use ELSEIF and then our final answer will simply be after ELSE. If you struggle to create these particular statements, you can always look at the example on the right-hand side to help you create these calculations. Let's now apply this particular calculated field to our chart. And I'll do it by dragging Value onto Color.
And as you can see, we have eight small products, three medium products and then seven large products. Let's now complicate this a bit further and separate Byresta from the other large products and call it very large. So I'll jump back into my calculated field.
And adjust my formula. And what I'm going to say is ELSEIF, the product name is equal to Byresta, then Very Large.
And unfortunately, I get an error and the reason I get an error is because when I include product name without a function before it, I have non-aggregate data. And so to fix this, I need to put in attribute and the attribute function allows me to convert the product name column into a value. And now this calculation is valid. I'll press OK.
And now Byresta has a different color to the other remaining large products. When using hard-coded numbers in IFELSE statements, you can run into trouble when you apply filters to your visualizations. Let me give you an example. Say I apply a date filter.
And I'll show this date filter and reduce the date range considerably.
In this reduced date range, a lot more products have less than 20,000 in sales. So my existing designation of small, medium and large changes. This is not ideal. What we would like is for a certain condition to stay constant, regardless of whether a filter is applied. To fix this problem, I'll show you a simple solution in the next lesson.