Was this page helpful?
Yes No
Have a comment? Please leave it here.Thanks for your feedback!

Home > 

Reference > Functions, Operators, & Data Types > Functions > Logical Functions

Logical Functions

Function expressions documented in this topic:

For documentation of the MIN, MAX, and ZN functions, see Number Functions.

 

Also:

 

CASE expression WHEN value1 THEN return1 WHEN value2 THEN return2...ELSE default return END

Use the CASE function to perform logical tests and return appropriate values. CASE is often easier to use than IIF or IF THEN ELSE. 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.

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

IIF(test, then, else, [unknown])

Use the IIF function to perform logical tests and return appropriate values. The first argument, test, must be a boolean: either a boolean field in the data source, or the result of a logical expression using operators (or a logical comparison of AND, OR, or NOT). If test evaluates to TRUE, then IIF returns the then value. If test evaluates to FALSE, then IIF returns the else value.

A boolean comparison may also yield the value UNKNOWN (neither TRUE nor FALSE), usually due to the presence of Null values in test. The final argument to IIF is returned in the event of an UNKNOWN result for the comparison. If this argument is left out, Null is returned.

Examples

IIF(7>5, "Seven is greater than five", "Seven is less than five")

IIF([Cost]>[Budget Cost], "Over Budget", "Under Budget")

IIF([Budget Sales]!=0,[Sales]/[Budget Sales],0)

IIF(Sales>=[Budget Sales], "Over Cost Budget and Over Sales Budget", "Over Cost Budget and Under Sales Budget","Under Cost Budget")

IF test THEN value END / IF test THEN value ELSE else END

Use the IF THEN ELSE function to perform logical tests and return appropriate values. The IF THEN ELSE function evaluates a sequence of test conditions and returns the value for the first condition that is true. If no condition is true, the ELSE value is returned. Each test must be a boolean: either be a boolean field in the data source or the result of a logical expression. The final ELSE is optional, but if it is not provided and there is no true test expression, then the function returns Null. All of the value expressions must be of the same type.

Examples

IF [Cost]>[Budget Cost] THEN "Over Budget" ELSE "Under Budget" END

IF [Budget Sales]!=0 THEN [Sales]/[Budget Sales] END

IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END

Use this version of the IF function to perform logical tests recursively. There is no built-in limit to the number of ELSEIFvalues you can use with an IF function, though individual databases may impose a limit on IF function complexity. While an IF function can be rewritten as a series of nested IIF statements, there are differences in how the expressions will be evaluated. In particular, an IIF statement distinguishes TRUE, FALSE and UNKNOWN, whereas an IF statement only worries about TRUE and not true (which includes both FALSE and UNKNOWN).

Example

When you create bins from a measure, Tableau creates bins of equal size by default. For example, say you have a measure that represents age. When you create bins from that measure, Tableau makes all the bins of equal size. You can specify how big you want the bins to be, but you cannot specify a separate range of values for each bin. A way around this constraint is to create a calculated field to define bins. Then you can create one bin for ages 0 - 20, another for ages 21 - 32, and so on. The following procedure shows how you could do that.

  1. Create a new calculated field by choosing Analysis > Create Calculated Field.

  2. Name the field Age Groups and type the following in the definition area

    IF
    [Age] < 21 THEN "Under 21"
    ELSEIF
    [Age] <= 32 THEN "21-32"
    ELSEIF
    [Age] <= 42 THEN "33-42"
    ELSEIF
    [Age] <= 52 THEN "43-52"
    ELSEIF
    [Age] <= 64 THEN "53-64"
    ELSE "65+"
    END
    
                        

    Confirm that the status message indicates that the formula is valid, and then click OK.

  3. From the Measures area of the Data pane, drag Number of Records to Rows.

  4. From the Dimensions area of the Data pane, drag Age Groups to Columns.

    The records are now divided among the six bins that you defined:

    Unfortunately, the Under 21 bin is at far right, when you would expect it to be at far left. Tableau's smart enough to put the bins with entirely numerical names in the right order, but it can't guess that the bin name beginning with "Under" belongs at the left. Fix the problem with a manual sort.

  5. Click the down arrow at the right side of the Age Groups field on Columns and then click Sort. Choose Manual and then move the Under 21 bin up to the top of the list:

    Your view is now complete.

IFNULL(expression1, expression2)

The IFNULL function returns the first expression if the result is not null, and returns the second expression if it is null.

Example

IFNULL([Profit], 0) = [Profit]

ISDATE(string)

The ISDATE function returns TRUE if the string argument can be converted to a date and FALSE if it cannot.

Examples

ISDATE("January 1, 2003") = TRUE

ISDATE("Jan 1 2003") = TRUE

ISDATE("1/1/03") = TRUE

ISDATE("Janxx 1 2003") = FALSE

ISNULL(expression)

The ISNULL function returns TRUE if the expression is Null and FALSE if it is not.

Example

The following example uses ISNULL in combination with IIF to replace null values with 0's.

IIF(ISNULL([Sales]), 0,[Sales] )

 

Additional use cases for logical functions

Use logical functions to create specific views from a data source without changing the source itself. For example, combine different members of a dimension, or filter a segment out of a view. Below are a few basic examples, using the IF statement with different types of logical operator. For more information about logical operators, see Operators.

Learn how to use logical functions to group members of a field, compare values, exclude values, and create virtual bins:

Group members of a field

In an OR statement, only one of the conditions in the statement needs to be true for the entire calculation to evaluate to true.

In this example, an OR operator is used to create a new region called East Coast, made up of members of the East and South regions:

IF [Region] = “East” OR [Region] = “South” THEN “East Coast” ELSE [Region] END

Using this calculation, your view will update to look like this:

Compare values

Logical statements that use the AND operator are useful when more than one field needs to be involved. In an AND statement, all conditions must evaluate to true for the overall calculation to be true.

The following calculation finds furniture sales for the Central region:

IF [Region] = “Central” AND [Product Category] = “Furniture” THEN [Sales] END

Use this calculation in a view to easily show how furniture sales in the Central region compare to total sales.

Exclude values

The calculation in this example uses the not equal to operator (<>). This operator is often used to exclude values or filter a member of a dimension out of a view.

The following formula calculates the total sales for all regions except East:

IF Region <> “East” THEN [Sales] END

Using this calculation, your view will update to look like this:

Create virtual bins

The greater than or equal to operator (>=) can be used to create virtual bins to categorize data in different ways. This type of calculation is very useful and is similar to how KPIs (Key Performance Indicators) are evaluated. In this calculation, the >= operator is used to group sales into large, medium, and small orders based on the dollar amount.

IF [Sales] >= 5000 THEN “Large Order”

ELSEIF [Sales] >= 1000 THEN “Medium Order”

ELSE “Small Order” END

Using this calculation, your view will update to look like this: