Logical Functions

Calculated fields in tableau are a feature that allow us to derive insights from the dataset that the dataset itself does not actually provide. It is a way of finding out further information from our data that may not be immediately available to the user. There are multiple different types of calculated fields in Tableau Desktop, 9 to be specific, and today I am going to focus on Logical Functions. 

Logical Functions (as it says on the tin) provide most of the logic for our calculated fields. They are the describing, connecting and linking ‘terms’ that allow us to form calculations of our data. 

The majority of these ‘logical’ calculations start with an IF statement...

IF

• Provides a foundation for logic in tableau 
• It tells tableau to look at a specific field and evaluate whether a condition is true or false 

AND

• Performs a logical conjunction on 2 expressions (combines 2 values together) 
• Allows you to add together multiple conditions within the same IF statement
• IF <expr1> AND <expr2> THEN <then> END

END

• Closes the calculation - tells tableau it is ready to run the expression

CASE

• Used to categorise values in a single field 
• Useful for grouping / renaming multiple values 
• “In the case of this field”
• CASE [RomanNumeral] WHEN ‘Ⅰ’ THEN 1 WHEN ‘ⅠⅠ’ THEN 2 ELSE 3 END 

WHEN 

• This is required in CASE expressions 
• Tells tableau to find the values that match the expression 
• WHEN the category is this, THEN return this 
• WHEN ‘Ⅰ’ THEN 1 

THEN 

• Required in IF / ELSEIF / CASE expressions 
• Whatever you write after THEN = the value tableau returns when a condition is met 
• IF [PROFIT] > 0 THEN ‘Profitable’  ELSEIF [PROFIT] = 0 THEN ‘Breakeven’ ELSE ‘Loss’ END 

ELSE 

• Optional part of IF / CASE statements 
• Returns a value when the expressions in your criteria are not met / returned 

ELSEIF

• Lets you check additional conditions once the initial IF has been tested 
• Useful when the logic needs to handle more than one scenario, beyond the initial IF statement 
• Used to specify a different condition that is not the original IF 
• IF [PROFIT] > 0 THEN ‘Profitable’  ELSEIF [PROFIT] = 0 THEN ‘Breakeven’ ELSE ‘Loss’ END 
• This lets us set another return for a condition that is not included in the original IF Statement. I.e., the original IF statement will return only profitable values, whereas the ELSEIF statement will return values where the profit is 0.
  

IFNULL 

• If there are null values in your data, you can use IFNULL to replace them
• For example, if you have a field where people have to select yes/no, some people may not select no - which leaves your data null 
• IFNULL ([PROFIT], 0)

ZN 

• Returns an expression if the record is not null, otherwise returns 0 
• ZN ([PROFIT])
• If the profit is greater than 0, return the value (which would be 1), otherwise return a 0 

ISNULL 

• Returns true if the expression is null 
• ISNULL ([PROFIT])
• Is the profit null? If so, true will return 

OR 

• Allows you to link 2 conditions which are separate
• IF <expr1> OR <expr2> THEN <xxxx> END 

NOT 

• Used as a boolean (true/false) output 

IN 

• Boolean function 
• Returns true if any value matches the expression 

IIF (Immediate IF) 

• Checks whether a condition is met, and returns one value if TRUE, another value if FALSE, and an optional third value or NULL if unknown 
• If this is true then return this, if not look at next expression 

ISDATE

• Returns true if a given string is a valid date 
• ISDATE (“2004-04-15”) = True