## Logical Functions

This article introduces logical functions and their uses in Tableau. It also demonstrates how to create a logical calculation using an example.

## Why use logical calculations

Logical calculations allow you to determine whether a certain condition is true or false (Boolean logic). For example, you might want to categorise values based on certain cut-offs.

A logical calculation might look something like this:

IF [Profit] > 0
THEN 'Profitable'
ELSEIF [Profit] = 0 THEN 'Break even'
ELSE 'Loss'
END

## Logical functions and operators available in Tableau

### AND

 Syntax AND 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" THEN "It's the apocalypse and footwear doesn't matter" END"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 TRUE (that is, not FALSE or NULL), then the result is TRUE. If either expression is NULL, then the result is NULL. In all other cases, the result is FALSE.If you create a calculation in which the result of an AND comparison is displayed on a worksheet, Tableau displays TRUE and FALSE. If you would like to change this, use the Format area in the format dialog.Note: The AND operator employs short circuit evaluation. This means that if the first expression is evaluated to be FALSE, then the second expression is not evaluated at all. This can be helpful if the second expression results in an error when the first expression is FALSE, because the second expression in this case is never evaluated.

### CASE

 Syntax CASE WHEN THEN WHEN THEN ... [ELSE ]END Output Depends on data type of the  values. Definition Evaluates the expression and compares it to the specified options (, , etc.). When a value that matches expression is encountered, CASE returns the corresponding return. If no match is found, the (optional) default is returned. If there is no default and no values match, then Null is returned. Example CASE [Season] WHEN 'Summer' THEN 'Sandals' WHEN 'Winter' THEN 'Boots' ELSE 'Sneakers' END"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 Sneakers." Notes See also IF and IIF.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.

### ELSE

 Syntax CASE WHEN THEN WHEN THEN ... [ELSE ]END 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' ELSEIF [Season] = "Winter" THEN 'Boots' ELSE 'Sneakers' ENDCASE [Season] WHEN 'Summer' THEN 'Sandals' WHEN 'Winter' THEN 'Boots' ELSE 'Sneakers' END Notes Used with CASE, WHEN, IF, ELSEIF, THEN and ENDELSE is optional with CASE and IF. In a calculation where ELSE is not specified, if none of the are true, the overall calculation will return null.ELSE does not require a condition (such as [Season] = "Winter") and can be thought of as a form of null handling.

### ELSEIF

 Syntax [ELSEIF THEN ] Definition An optional piece of an IF expression used to specify additional conditions beyond the initial IF. Example IF [Season] = "Summer" THEN 'Sandals' ELSEIF [Season] = "Winter" THEN 'Boots' ELSEIF [Season] = "Spring" THEN 'Sneakers' ELSEIF [Season] = "Autumn" THEN 'Sneakers'ELSE 'Bare feet' END Notes Used with IF, THEN, ELSE and ENDELSEIF can be thought of as additional IF clauses. ELSEIF is optional and can be repeated multiple times.Unlike ELSE, ELSEIF requires a condition (such as [Season] = "Winter").

### END

 Definition Used to close an IF or CASE expression. Example IF [Season] = "Summer" THEN 'Sandals' ELSEIF [Season] = "Winter" THEN 'Boots' ELSE 'Sneakers' END"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] WHEN 'Summer' THEN 'Sandals' WHEN 'Winter' THEN 'Boots' ELSE 'Sneakers' END"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 Sneakers." Notes Used with CASE, WHEN, IF, ELSEIF, THEN and ELSE.

### IF

 Syntax IF THEN [ELSEIF THEN ...][ELSE ] END Output Depends on data type of the  values. Definition Tests a series of expressions and returns the value for the first true . Example IF [Season] = "Summer" THEN 'Sandals' ELSEIF [Season] = "Winter" THEN 'Boots' ELSE 'Sneakers' END"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 See also IF and IIF.Used with ELSEIF, THEN, ELSE and END

### IFNULL

 Syntax IFNULL(expr1, expr2) Output Depends on the data type of the values. Definition Returns if it's non-null, otherwise 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. IFNULL always returns a value. ISNULL returns a boolean (true or false).See also ZN.

### IIF

 Syntax IIF(, , , []) Output Depends on the data type of the values in the expression. Definition Checks whether a condition is met () and returns if the test is true,  if the test is false and an optional value for 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', IIF('Season' = 'Winter', 'Boots', 'Other footwear'))"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', IIF('Season' = 'Winter', 'Boots', IIF('Season' = 'Spring', 'Sneakers', 'Other footwear') ))"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 See also IF andCASE.IIF doesn't have an equivalent to ELSEIF (like IF) or repeated WHEN clauses (like CASE). Instead, multiple tests can be evaluated sequentially by nesting IIF statements as the  element. The first (outermost) true is returned.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:IIF('A' = 'A', 'Red', IIF('B' = 'B', 'Orange', IIF('C' = 'D', 'Yellow', 'Green')))

### IN

 Syntax IN Output Boolean (true or false) Definition Returns TRUE if any value in matches any value in . 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 can be a set, list of literal values or combined field.See also WHEN.

### ISDATE

 Syntax ISDATE(string) Output Boolean (true or false) Definition Returns true if a 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:ISDATE("2018-09-22") = TRUEISDATE("2018-22-09") = FALSEIn the UK: ISDATE("2018-09-22") = FALSEISDATE("2018-22-09") = TRUE

### ISNULL

 Syntax ISNULL(expression) Output Boolean (true or false) Definition Returns true if the is NULL (does not contain valid data). Example ISNULL([Assigned Room])"Is the Assigned Room field null?" Notes Compare with IFNULL. IFNULL always returns a value. ISNULL returns a boolean.See also ZN.

### MAX

 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.MAX can also be applied to a single field as an aggregation. Example MAX(4,7) = 7MAX(#3/25/1986#, #2/20/2021#) = #2/20/2021# MAX([Name]) = "Zander" Notes For stringsMAX is usually the value that comes last in alphabetical order.For database data sources, the MAX string value is highest in the sort sequence defined by the database for that column.For datesFor dates, the MAX is the most recent date. If MAX is an aggregation, the result will not have a date hierarchy. If MAX is a comparison, the result will retain the date hierarchy.As an aggregationMAX(expression) is an aggregate function and returns a single aggregated result. This displays as AGG(expression) in the viz.As a comparisonMAX(expr1, expr2) compares the two values and returns a row-level value.See also MIN.

### MIN

 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.MIN can also be applied to a single field as an aggregation. Example MIN(4,7) = 4MIN(#3/25/1986#, #2/20/2021#) = #3/25/1986#MIN([Name]) = "Abebi" Notes For stringsMIN is usually the value that comes first in alphabetical order.For database data sources, the MIN string value is lowest in the sort sequence defined by the database for that column.For datesFor dates, the MIN is the earliest date. If MIN is an aggregation, the result will not have a date hierarchy. If MIN is a comparison, the result will retain the date hierarchy.As an aggregationMIN(expression) is an aggregate function and returns a single aggregated result. This displays as AGG(expression) in the viz.As a comparisonMIN(expr1, expr2) compares the two values and returns a row-level value.See also MAX.

### NOT

 Syntax NOT  Output Boolean (true or false) Definition Performs logical negation on an expression. Example IF NOT [Season] = "Summer" THEN 'Don't wear sandals' ELSE 'Wear sandals' END"If Season doesn't equal Summer, then return Don't wear sandals. If not, return Wear sandals." Notes Often used with IF and IIF. See also AND and OR.

### OR

 Syntax OR Output Boolean (true or false) Definition Performs a logical disjunction on two expressions. Example IF [Season] = "Spring" OR [Season] = "Fall" THEN "Sneakers" END"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 TRUE, then the result is TRUE. If both expressions are FALSE, then the result is FALSE. If both expressions are NULL, then the result is NULL.If you create a calculation that displays the result of an OR comparison on a worksheet, Tableau displays TRUE and FALSE. If you would like to change this, use the Format area in the format dialog.Note: The OR operator employs short circuit evaluation. This means that if the first expression is evaluated to be TRUE, then the second expression is not evaluated at all. This can be helpful if the second expression results in an error when the first expression is TRUE, because the second expression in this case is never evaluated.

### THEN

 Syntax IF THEN [ELSEIF THEN ...] [ELSE ] END 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' ELSEIF [Season] = "Winter" THEN 'Boots' ELSE 'Sneakers' END"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] WHEN 'Summer' THEN 'Sandals' WHEN 'Winter' THEN 'Boots' ELSE 'Sneakers' END"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 Sneakers." Notes Used with CASE, WHEN, IF, ELSEIF, THEN, ELSE and END

### WHEN

 Syntax CASE WHEN THEN WHEN THEN ... [ELSE ]END Definition A required part of a CASE expression. Finds the first that matches and returns the corresponding . Example CASE [Season] WHEN 'Summer' THEN 'Sandals' WHEN 'Winter' THEN 'Boots' ELSE 'Sneakers' END"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 Sneakers." Notes Used with CASE, THEN, ELSE and END.CASE also supports WHEN IN construction, such as:CASE WHEN IN THEN WHEN IN THEN ... ELSE ENDThe values that WHEN IN compare to must be a set, list of literal values or combined field. See also IN.

### ZN

 Syntax ZN(expression) Output Depends on the data type of the , or 0. Definition Returns 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 ZN is a specialised case of IFNULL where the alternative if the expression is null is always 0 rather than being specified in the calculation.ZN is especially useful when performing additional calculations, and a null would render the entire calculation null. However, use caution interpreting these results as null is not always synonymous with 0 and could represent missing data.See also ISNULL.

Note: Some of these are actually logical operators and appear in black, not blue. For more information, see Operator syntax.

Note on CASE, IF and IIF

CASE is often easier to use than IF or IIF. In many instances, IF, IIF and CASE can be used interchangeably. A CASE statement can always be rewritten as an IF statement, although the CASE function will generally be more concise and may be easier to understand. However, not all IF statements can be written as CASE statements, because each ELSEIF could refer to a different field.

Tip: Let's compare an example using the same logic across these three functions:

 CASE IF IIF CASE [Region]WHEN 'West' THEN 1WHEN 'East' THEN 2WHEN 'South' THEN 3WHEN 'North' Then 4ELSE 5END IF [Region] = 'West' THEN 1ELSEIF [Region] = 'East' THEN 2ELSEIF [Region] = 'South' THEN 3ELSEIF [Region] = 'North' THEN 4ELSE 5END IIF([Region] = 'West', 1, IIF([Region] = 'East', 2, IIF([Region] = 'South', 3, IIF([Region] = 'North', 4, 5, 0) ) )) The CASE structure is very simple and easy to write and understand. However, the expression (here, [Region]) cannot be a logical operation, unlike with IF or IIF.Nulls are handled the same for CASE as they are for IF. The IF THEN structure permits multiple ELSEIF clauses, which makes multiple evaluations much easier than with IIF.Null handling: any unknown (null) results are handled by the same ELSE clause as false results. Here, any region other than the four specified (including null) will be assigned a value of 5. The IIF structure handles unknown results differently than false results and has a different syntax from IF. The trade-off for specific null handling is nesting, as above, which can be clumsy to write and understand.Null handling: any non-null region other than the four specified will be assigned a value of 5, but a null value will be assigned a 0.

Note: Often, you can use a group to get the same results as a complicated case function. Test to see whether one option is more performant than the other.

### Examples

CASE functions can be useful for scenarios such as realising:

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

or giving the end user the ability to select which measure to view in a chart when used with a parameter(Link opens in a new window):

CASE [Choose a Measure]
WHEN "Sales" THEN SUM([Sales])
WHEN "Profit" THEN AVG([Profit])
WHEN "Quantity" THEN COUNT([Quantity])
WHEN "Shipping Cost" THEN MEDIAN([Shipping Cost])
END

## Create a logical calculation

Follow along with the steps below to learn how to create a logical calculation.

1. In Tableau Desktop, connect to the Sample - Superstore saved data source, which comes with Tableau.
2. Navigate to a worksheet.
3. From the Data pane, drag State to the Rows shelf.
4. From the Data pane, drag Category to the Rows shelf and place it to the right of State.
5. From the Data pane, drag Sales to the Columns shelf.
6. Select Analysis > Create Calculated Field.
7. 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.
8. The new calculated field appears under Measures in the Data pane. Just like your other fields, you can use it in onr or more visualisations.

9. From the Data pane, drag KPI to Colour on the Marks card.

You can now see which categories are losing money in each state.

## See Also

Visualise Key Progress Indicators

Functions in Tableau

Tableau Functions (by Category)

Tableau Functions (Alphabetical)

Thanks for your feedback!Your feedback has been successfully submitted. Thank you!