Logical Functions
This article introduces logical functions and their uses in Tableau. It also demonstrates how to create a logical calculation using an example.
Logical calculations allow you to determine if a certain condition is true or false (Boolean logic). For example, you might want to categorize values based on certain cutoffs.
A logical calculation might look something like this:
IF [Profit] > 0
THEN 'Profitable'
ELSEIF [Profit] = 0 THEN 'Break even'
ELSE 'Loss'
END
Syntax | <expr1> AND <expr2> |
Definition | Performs a logical conjunction on two expressions. (If both sides are true, the logical test returns true.) |
Output | Boolean (true or false) |
Example | IF [Season] = "Spring" AND "[Season] = "Fall" "If both (Season = Spring) and (Season = Fall) are true simultaneously, then return It's the apocalypse and footwear doesn't matter". |
Notes | Often used with IF and IIF. See also NOT and OR. If both expressions are If you create a calculation in which the result of an Note: The |
Syntax | CASE <expression>
|
Output | Depends on data type of the <then> values. |
Definition | Evaluates the |
Example | "Look at the Season field. If the value is Summer, then return Sandals. If the value is Winter, then return Boots. If none of the options in the calculation match what is in the Season field, return a Sneakers." |
Notes | Used with WHEN, THEN, ELSE, and END. Tip: Many times you can use a group to get the same results as a complicated CASE function, or use CASE to replace native grouping functionality, such as in the previous example. You may want to test which is more performant for your scenario. |
Syntax | CASE <expression>
|
Definition | An optional piece of an IF or CASE expression used to specify a default value to return if none of the tested expressions are true. |
Example | IF [Season] = "Summer" THEN 'Sandals' CASE [Season] |
Notes | Used with CASE, WHEN, IF, ELSEIF, THEN, and END
|
Syntax | [ELSEIF <test2> THEN <then2>] |
Definition | An optional piece of an IF expression used to specify additional conditions beyond the initial IF. |
Example | IF [Season] = "Summer" THEN 'Sandals' |
Notes | Used with IF, THEN, ELSE, and END
Unlike |
Definition | Used to close an IF or CASE expression. |
Example | IF [Season] = "Summer" THEN 'Sandals' "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If neither of the expressions are true, return Sneakers." CASE [Season] "Look at the Season field. If the value is Summer, then return Sandals. If the value is Winter, then return Boots. If none of the options in the calculation match what is in the Season field, return a Sneakers." |
Notes |
Syntax | IF <test1> THEN <then1> |
Output | Depends on data type of the <then> values. |
Definition | Tests a series of expressions and returns the |
Example | IF [Season] = "Summer" THEN 'Sandals' "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If neither of the expressions are true, return Sneakers." |
Notes |
Syntax | IFNULL(expr1, expr2) |
Output | Depends on the data type of the <expr> values. |
Definition | Returns |
Example | IFNULL([Assigned Room], "TBD") "If the Assigned Room field isn't null, return its value. If the Assigned room field is null, return TBD instead." |
Notes | Compare with ISNULL. See also ZN. |
Syntax | IIF(<test>, <then>, <else>, [<unknown>]) |
Output | Depends on the data type of the values in the expression. |
Definition | Checks whether a condition is met (<test> ), and returns <then> if the test is true, <else> if the test is false, and an optional value for <unknown> if the test is null. If the optional unknown isn't specified, IIF returns null. |
Example | IIF([Season] = 'Summer', 'Sandals', 'Other footwear') "If Season = Summer, then return Sandals. If not, return Other footwear" IIF([Season] = 'Summer', 'Sandals', "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If neither are true, return Sneakers." IIF('Season' = 'Summer', 'Sandals', "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If none of the expressions are true, return Sneakers." |
Notes |
That is to say, in the calculation below, the result will be Red, not Orange, because the expression stops being evaluated as soon as A=A is evaluated as true:
|
Syntax | <expr1> IN <expr2> |
Output | Boolean (true or false) |
Definition |
Returns TRUE if any value in <expr1> matches any value in <expr2> . |
Example | SUM([Cost]) IN (1000, 15, 200) "Is the value of the Cost field 1000, 15, or 200?" [Field] IN [Set] "Is the value of the field present in the set?" |
Notes | The values in See also WHEN. |
ISDATE
Syntax | ISDATE(string) |
Output | Boolean (true or false) |
Definition | Returns true if a <string> is a valid date. The input expression must be a string (text) field. |
Example | ISDATE("2018-09-22") "Is the string 2018-09-22 a properly formatted date?" |
Notes | What is considered a valid date depends on the locale(Link opens in a new window) of the system evaluating the calculation. For example: In the USA:
In the UK:
|
Syntax | ISNULL(expression) |
Output | Boolean (true or false) |
Definition | Returns true if the |
Example | ISNULL([Assigned Room]) "Is the Assigned Room field null?" |
Notes | Compare with IFNULL. See also ZN. |
Syntax | MAX(expression) or MAX(expr1, expr2) |
Output | Same data type as the argument, or NULL if any part of the argument is null. |
Definition | Returns the maximum of the two arguments, which must be of the same data type.
|
Example | MAX(4,7) = 7 |
Notes | For strings
For database data sources, the For dates For dates, the As an aggregation
As a comparison
See also |
Syntax | MIN(expression) or MIN(expr1, expr2) |
Output | Same data type as the argument, or NULL if any part of the argument is null. |
Definition | Returns the minimum of the two arguments, which must be of the same data type.
|
Example | MIN(4,7) = 4 |
Notes | For strings
For database data sources, the For dates For dates, the As an aggregation
As a comparison
See also |
Syntax | NOT <expression> |
Output | Boolean (true or false) |
Definition | Performs logical negation on an expression. |
Example | IF NOT [Season] = "Summer" "If Season doesn't equal Summer, then return Don't wear sandals. If not, return Wear sandals." |
Notes |
Syntax | <expr1> OR <expr2> |
Output | Boolean (true or false) |
Definition | Performs a logical disjunction on two expressions. |
Example | IF [Season] = "Spring" OR [Season] = "Fall" "If either (Season = Spring) or (Season = Fall) is true, then return Sneakers." |
Notes | Often used with IF and IIF. See also AND and NOT. If either expression
is If you create
a calculation which displays the result of an Note: The |
Syntax | IF <test1> THEN <then1>
|
Definition | A required part of an IF , ELSEIF , or CASE expression, used to define what result to return if a specific value or test is true. |
Example | IF [Season] = "Summer" THEN 'Sandals' "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If neither of the expressions are true, return Sneakers." CASE [Season] "Look at the Season field. If the value is Summer, then return Sandals. If the value is Winter, then return Boots. If none of the options in the calculation match what is in the Season field, return a Sneakers." |
Notes |
Syntax | CASE <expression>
|
Definition | A required part of a CASE expression. Finds the first <value> that matches <expression> and returns the corresponding <then> . |
Example | CASE [Season] "Look at the Season field. If the value is Summer, then return Sandals. If the value is Winter, then return Boots. If none of the options in the calculation match what is in the Season field, return a Sneakers." |
Notes | Used with CASE, THEN, ELSE, and END.
CASE <expression> The values that |
Syntax | ZN(expression) |
Output | Depends on the data type of the <expression> , or 0. |
Definition | Returns <expression> if it isn't null, otherwise returns zero. |
Example | ZN([Test Grade]) "If the test grade isn't null, return its value. If the test grade is null, return 0." |
Notes |
See also ISNULL. |
Note: some of these are actually logical operators and appear in black, not blue. For more information, see Operator syntax.
Follow along with the steps below to learn how to create a logical calculation.
- In Tableau Desktop, connect to the Sample - Superstore saved data source, which comes with Tableau.
- Navigate to a worksheet.
- From the Data pane, drag State to the Rows shelf.
- From the Data pane, drag Category to the Rows shelf and place it to the right of State.
- From the Data pane, drag Sales to the Columns shelf.
- Select Analysis > Create Calculated Field.
- In the calculation editor that opens, do the following:
- Name the calculated field KPI.
- Enter the following formula:
SUM([Profit]) > 0
This calculation quickly checks if a member is great than zero. If so, it returns true; if not, it returns false.
- When finished, click OK.
- From the Data pane, drag KPI to Color on the Marks card.
You can now see which categories are losing money in each state.
The new calculated field appears under Measures in the Data pane. Just like your other fields, you can use it in onr or more visualizations.
See Also
Visualize Key Progress Indicators