Sign in or start a free trial to avail of this feature.
7. Complex Logical Functions
In this lesson, we continue looking at conditional functions, seeing how you can build more complex conditions using the AND and OR functions.
Lesson Goal (00:10)
The goal of this lesson is to create more complex conditions by adding OR statements to the existing IF condition.
Modifying the IF Statement (00:17)
Our existing IF statement assigns values to the Product Category field based on a logical condition. This condition can include the value of the Product name. For example, our dataset contains one product, Melacaine, with significantly higher sales than the other products. We adjust our IF statement so that this product has its own category, called Very Large. We then next the existing IF statement inside the new one, so all the other products are classified as before.
Counting Customers for Each Product (01:20)
We want to adjust the product category IF statement so that a product’s category is based on its total sales and its total number of customers. To achieve this goal, we create a column counting the number of customers for each product. We do this using CALCULATE. We use DISTINCTCOUNT to count the number of customers each product has. Then, we use RELATEDTABLE to filter the transactions table to show an individual product, as we did previously.
Creating an OR Condition (02:28)
When testing multiple conditions, we can use AND or OR. These functions are used as part of the logical test in an IF function. They both accept two logical tests. AND returns true if both its logical tests are true, while OR returns true if one or both of its logical tests is true.
In our case, we want a product to be classified as small if its sales or its customer numbers are below specified thresholds, so we adjust our IF statement to use OR as the logical test, and test the total sales and customer count. We perform a similar change for the other nested IF statement, so that the IF statement now categorizes each product based on its total sales and its customer count.
In the previous lesson, we used an IF statement to categorize our products as either small, medium, or large. In this lesson, we'll create more complex conditions by adding OR statements to the existing IF condition.
As we look at the graph, it's clear that Melacaine has the largest sales by some distance.
The pharma company originally wanted to create three different marketing campaigns for three different product categories.
However, they've now decided to separate Melacaine from the other large products, due to the sales gap.
To highlight this gap, we'll create a new special category called very large.
To do this, we'll navigate to data view and select the Product Size column.
We'll modify the formula to create our new category. I'm going to paste this new formula and run through the changes. Note there's a new IF condition at the very start. This condition specifically states that the product, Melacaine should return the category, very large.
All other products will run through the existing IF statement.
We'll press enter to update the column.
As we return to report view, we can see that Melacaine now has its own category of very large.
At the moment, a product's category is only based on the sales field. Let's also consider the number of customers for each product.
If a product has large sales numbers that are concentrated among a small number of customers, we do not want to consider that product to be large.
Let's navigate back to the data view, and create a new column to count the number of customers for each product.
As I mentioned in the previous lesson, this calculation isn't strictly necessary, but will keep our conditional statement easier to read. We'll select a new column and call it Customer Count.
The principle here is similar to the Product Sales column.
We'll use the CALCULATE function, and then enter distinct count of customer IDs as our calculation.
This counts the number of unique customer IDs in the data set.
As before, we'll use a related table to filter for a single product.
We'll then press enter to create the column.
We now have a count of the number of customers for each product, which we can use to update our categories.
When we have two conditions such as this, we can use the AND function, as well as the OR function.
In this case, we want products with sales below 20,000, or fewer than 70 customers to be classified as small.
We also want products with less than 100 customers, or sales below 50,000 to be classified as medium.
As a result, we'll add these conditions using an OR function.
We'll select the Product Size column, and expand the formula bar.
We'll now add an OR statement within the 2nd IF statement.
OR takes two logical tests and returns true if either or both of those tests is true.
We want to include the existing test of sales, and add a test of customer count being less than 70.
We'll now add a close bracket for the OR statement.
Now, any product with less than 20,000 in sales or less than 70 customers will be classified as small.
We'll do something similar for the medium test.
We'll add an OR statement, and add the condition of customer count being less than 100.
Again, we need to add the close brackets.
We can now press enter to accept the new formula.
If we move back to report view, we can see that the chart reflects the updated categories.
You may notice that there are more products in the small category than there were previously.
We've now created more complex logical tests using the OR function.
As you can see, IF statements and logical functions can be quite powerful, and the logic behind them is usually not difficult to understand. However, it's also clear that long IF statements can be slightly difficult to read, interpret, and format correctly, especially when you have multiple conditions to check.
In the next lesson, we'll see how to address these situations by using the SWITCH function.