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:

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!