Tableau Logical Functions

Tableau Logical Functions

Why do we use logical calculations?

Logical calculations allow you to determine if a certain condition is true or false (boolean logic). For example, you might want to quickly see if sales for each country you distribute your merchandise to were above or below a certain threshold.

Function	Syntax	Description
IN	<expr1> IN <expr2>	Returns TRUE if any value in <expr1> matches any value in <expr2>. The values in <expr1> can be a Set, list of literal values, or a combined field. Examples: SUM([Cost]) IN (1000, 15, 200) [SET] IN [COMBINED FIELD]
AND	IF <expr1> AND <expr2> THEN <then> END	Performs a logical conjunction on two expressions. Example: IF (ATTR([Market]) = "Africa" AND SUM([Sales]) > [Emerging Threshold] )THEN "Well Performing"
CASE	CASE <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> ... ELSE <default return> END	Performs logical tests and returns appropriate values. The CASE function evaluates expression, compares it to a sequence of values, value1, value2, etc., and returns a result. When a value that matches expression is encountered, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, then Null is returned. CASE is often easier to use than IIF or IF THEN ELSE. Typically, you use an IF function to perform a sequence of arbitrary tests, and you use a CASE function to search for a match to an expression. But a CASE function can always be rewritten as an IF function , although the CASE function will generally be more concise. Many times you can use a group to get the same results as a complicated case function. Examples: CASE [Region] WHEN 'West' THEN 1 WHEN 'East' THEN 2 ELSE 3 END CASE LEFT(DATENAME('weekday',[Order Date]),3) WHEN 'Sun' THEN 0 WHEN 'Mon' THEN 1 WHEN 'Tue' THEN 2 WHEN 'Wed' THEN 3 WHEN 'Thu' THEN 4 WHEN 'Fri' THEN 5 WHEN 'Sat' THEN 6 END
ELSE	IF <expr> THEN <then> ELSE <else> END	Tests a series of expressions returning the <then> value for the first true <expr>. Example: If [Profit] > 0 THEN 'Profitable' ELSE 'Loss' END
ELSEIF	IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] [ELSE <else>] END	Tests a series of expressions returning the <then> value for the first true <expr>. Example: IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END
END	IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] [ELSE <else>] END	Tests a series of expressions returning the <then> value for the first true <expr>. Must be placed at the end of an expression. Example: IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END
IF	IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] [ELSE <else>] END	Tests a series of expressions returning the <then> value for the first true <expr>. Example: IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END
IFNULL	IFNULL(expr1, expr2)	Returns <expr1> if it is not null, otherwise returns <expr2>. Example: IFNULL([Profit], 0)
IIF	IIF(test, then, else, [unknown])	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. Example: IIF([Profit] > 0, 'Profit', 'Loss')
ISDATE	ISDATE(string)	Returns true if a given string is a valid date. Example: ISDATE("2004-04-15") = True
ISNULL	ISNULL(expression)	Returns true if the expression is NULL (does not contain valid data). Example: ISNULL([Profit])
MAX	MAX(expression) or Max(expr1, expr2)	Returns the maximum of a single expression across all records or the maximum of two expressions for each record. Example: MAX([Sales])
MIN	MIN(expression) or MIN(expr1, expr2)	Returns the minimum of an expression across all records or the minimum of two expressions for each record. Example: MIN([Profit])
NOT	IF NOT <expr> THEN <then> END	Performs logical negation on an expression. Example: IF NOT [Profit] > 0 THEN "Unprofitable" END
OR	IF <expr1> OR <expr2> THEN <then> END	Performs a logical disjunction on two expressions. Example: IF [Profit] < 0 OR [Profit] = 0 THEN "Needs Improvement" END
THEN	IF <expre> THEN <then> [ELSEIF ,expr2> THEN <then2>...] [ELSE <else>] END	Tests a series of expressions returning the <then> value for the first true <expr>. Example: IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Break even' ELSE 'unprofitable' END
WHEN	CASE <expr> WHEN <Value1> THEN <return1> ... [ELSE <else>] END	Finds the first <value> that matches <expr> and returns the corresponding <return>. Example: CASE [RomanNumberal] WHEN 'I' THEN 1 WHEN 'II' THEN 2 ELSE 3 END
ZN	ZN(expression)	Returns <expression> if it is not null, otherwise returns zero. Example: ZN([Profit])