If you create a calculated field in Tableau, you are able to choose from a variety of functions: Number, Date, String, Type Conversion, Logical, Aggregate, User, Table Calculation and Spatial. In this post, I will overview Logical calculations that Tableau has to offer.
When you start writing a logical calculation in the calculated field window, you will see this:
![](https://www.thedataschool.co.uk/content/images/2021/04/image-44.png)
I have grouped these formulas into a couple of groups for an easier explanation.
IF statement
IF - beginning of your condition
(NOT) - optional to use with IF if you want a negative condition
(AND / OR) - optional; to use with IF if you want two or more conditions to be true (AND); or one of two or more conditions to be true (OR)
THEN - returned value if your condition is met
(ELSEIF) - optional; beginning of your other condition
(ELSE) - optional; returned value if your condition is not met
END - closing your condition
Example:
IF SUM([PROFIT]) > SUM([SALES]) AND SUM([TARGET]) < SUM([QUANTITY])
THEN 'SUCCESS'
ELSE 'FAIL'
END
I discovered a very useful function that can reduce your efforts whilst writing a simple IF statement:
IIF(condition,value if True,value if False)
Example:
IFF(([PROFIT]) > SUM([SALES]),'SUCCESS','FAIL')
CASE statement
CASE - very similar to IF statement, but CASE statements can’t take Boolean calculations
WHEN - beginning of your condition with CASE statement
THEN - returned value if your condition is met
(ELSE) - optional; returned value if your condition is not met
END - closing your condition
Example:
CASE [Measure Switch]
WHEN 'Sales' THEN SUM([Sales])
WHEN 'Profit' THEN SUM([Profit])
WHEN 'Quantity' THEN SUM([Quantity])
END
Note that Measure Switch was a parameter with listed fields of Sales, Profit and Quantity.
Is it null or is it not?
ISNULL(expr) - looking for nulls; returns True or False
ZN(expr) - looking for not nulls; returns your expr if it is not null and 0 otherwise
IFNULL(expr1,expr2) - looking for not nulls; returns expr if it is not null and expr1 otherwise
Others
expr1 IN expr2 - looking for matches between two expressions; expr2 can be set, group or list of values; returns True if expr1 has matches in expr2 and False otherwise
ISDATE(string) - looking for valid date; returns True if string is a valid date and False otherwise (e.g. any year containing 31 Feb would be false)
MAX(expr) - looking for maximum value
MAX(expr1,expr2) - looking for maximum value between the two expressions
MIN(expr) - looking for minimum value
MIN(expr1,expr2) - looking for minimum value between the two expressions
Do check Tableau explanations yourself for every formula it has. Simply select little triangle on the right hand side of the calculated field window, and browse!