3. AND() and OR() Functions in Tableau

Overview

When setting multiple conditions in a logical function, we use either the AND() function or OR() function to do so. We will learn how both of these functions work in this lesson.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

 

Multiple conditions with AND() and OR()

- These two functions are used when more than one condition is needed in a IF/ELSE statement
- AND() is used when all conditions must be met
- OR() is used when only one condition must be met

Transcript

In our current example, we have a single condition for if and else-if statements. You might be asking how do I create multiple conditions? Well, it's pretty easy. You use the and function or the, or function. The and function means that both conditions have to be satisfied to return in this case small. The or function means that only one of the two conditions have to be satisfied. Say, for example, I want to change the definition of small to mean either sales of less than 20,000 or a customer count of less than 80.

To do this I can simply add or function, repeat the fixed function as before. I'll fix on product name as before. But this time around the function would be customer count which is a calculated field. And then close the brackets and make sure that the customer count is less than 80. For medium I'm going to include the exact same, taking or, applying fixed on product name, with the customer count within brackets and less than 105.

I'm now going to press okay and apply this to my sheet. And as you can see, this significantly moves the allocation for each individual product.

Thankfully, as I scroll these values don't change although their positions may do.

Going back to our formula, you might be asking yourself how do I include a third condition in my if-else statement, but it's very easy. You can simply add another line and write or including an additional expression here. In fact, you can chain together as many of these expressions as you wish using either or, or and depending on what criteria you want your conditions to hold. Typically when writing, if-else statements I use not, or, and, and as the functions that I use the most. Not simply acts as an inverse to your existing expression. For example, I can put not before or and then change the version of the sign. So instead of saying less than 80, it's greater than or equal to 80.

And this will return the exact same set of values that I had previously.

Most of the time, you won't really need to use not but it can be more intuitive on occasion and help you structure the correct expression. As you've seen before, the calculated fields such as customer count can be used in other calculated fields in this case, product size. Just be sure when using these calculated fields that you don't run into non-aggregated data errors as we've seen in the past. Thankfully, in this case customer count has CountD function.

And so the non aggregated data problem doesn't arise.

>
Formulas in Tableau
Further Formulas in Tableau

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial