Logical functions in Tableau enable conditional evaluations to create calculated fields that return specific values based on defined criteria. In this post, I will explore functions such as IF, ELSEIF, IIF, OR, AND, ZN, IFNULL, ISNULL, IN, CASE and WHEN. I am using the MakeOverMonday 2024 Week 4 dataset, which focuses on the highest-paid athletes, to analyse their total earnings across various sports.
The IF function
The IF statement evaluates a condition and returns a specific value if the condition is true, or a different value if the condition is false. For example, to have Tableau return a null value for all basketball players while showing the total earnings for other athletes, you can use the following expression:
The ELSEIF function
The ELSEIF function works with the IF statement to evaluate multiple conditions in sequence, allowing for a series of conditional checks. This function lets you specify various criteria to refine your results. For instance, to have Tableau return a null value for basketball players, auto racers, and football player Aaron Donald while displaying the total earnings for all other athletes, you can use the following expression:
The IIF function
The IIF function is a simplified version of the IF statement. It takes three arguments: the condition, the value to return if the condition is true, and the value to return if the condition is false. So, instead of writing the IF statement like this:
We can write a shorter version:
However, note that you cannot do multiple checks with this function.
The OR function
The OR function evaluates multiple conditions and returns true if at least one of the conditions is true. For the function to return true, only one of the specified conditions needs to be met.
The AND function
The AND function evaluates multiple conditions and returns true only if all specified conditions are true. If any condition is false, the function returns false. For example, if you want Tableau to return a null value for both basketball players and athlete Lionel Messi, the AND function will not work as intended. This is because both conditions need to be true simultaneously and apply to the same row of data.
The condition will work if I ask Tableau to return a null value for the basketball player Anthony Davis, as both "basketball" and "Anthony Davis" are on the same row.
The ZN function
The ZN function handles null values by converting them into zeros. If the input value is null, ZN returns 0; otherwise, it returns the original value. This function is particularly useful for ensuring that calculations or visualizations involving numerical data remain accurate, as it prevents null values from causing errors or gaps. In this example, the ZN function replaces null values resulting from the previous IF statement with 0.
The IFNULL function
Similar to the ZN function, the IFNULL function is used to handle null values by returning a specified alternative value if the original value is null. If the original value is not null, IFNULL returns the original value.
The ISNULL function
The ISNULL function checks whether a given value is null and returns true if it is, or false if it is not. It is used to identify null values within the data.
The IN function
The IN function determines if a specific value exists within a defined list of values. It returns true if the value is found in the list and false if it is not.
The CASE and WHEN functions
The CASE function simplifies multiple IF statements by evaluating a single expression against several possible values. It returns the result corresponding to the first match. If no match is found, it can return a default value if specified. The WHEN function is used within the CASE statement to define each condition. It checks if the CASE expression matches a particular value, returning the associated result if a match is found.
