Today we learnt about all the possibilities when it comes to creating calculated fields in Tableau. This blog will focus on some of the options that fall under the "Logical" category of functions.
Logical calculated fields are generally used when you want to test existing field against certain criteria. For example, if we have a dataset containing information on every single order from an online shop, we may want to generate a new field which groups these orders into distinct categories based on the number of products ordered. I may want to classify orders that contained 10 or more items as "High Volume", between 5 to 9 items as "Mid Volume" and less than 5 items as "Low Volume."
To do this, I require the logical statements IF, ELSE, and ELSEIF. I can use theses statements to write the following rule to create a calculated field:
I first name my new calculated field to be created as "Order volume". The first logical statement (lines 1 and 2) tells Tableau to look through the Quantity field of each record in my dataset, and if the number is greater than or equal to 10 to return the string "High volume" in the new Order volume field.
Lines 3 and 4 tell Tableau to look through the Quantity field and if the number is greater than 5 but less than 10 to return "Mid volume".
Line 5 tells Tableau that for records that do not satisfy our criteria, (i.e. Quantity less than 5) to return "Low volume". Finishing a formula for a calculated rule with "END" is also necessary.
It is crucial to remember that Tableau reads and applies these statements from top to bottom, ordering statements incorrectly can lead to errors in creating calculated fields.
Now that I have created a calculated field which groups my orders into categories based on volume, I can analyse my data in new ways. For example, I can see which levels of order volume are the most profitable:
With the help of my new calculated field I can see that while high volume orders have made the lowest amount of net profit, they actually have the greatest profit ratio.
Other logical fields include ISDATE and ISNULL. The former returns True if the given string in a field is a valid date, while the former returns True if the given expression is null. Both of these functions are very useful when checking the completeness of a dataset. The function IN is also useful when evaluated whether a the contents of a field are part of another expression, such as a set or a list of values.