Using Logical Functions In Tableau

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.

 

Step 1: To explore and create the logical functions, use the Calculated Fields. To bring up the Calculated Field window, right-click anywhere in the Data window i.e. sidebar to bring up that menu. Select Create calculated field…

 

right click create calculated field

 

Step 2: The above step opens a calculation editor which lists all the functions that is available in Tableau. If you right-clicked on a particular dimension or measure to create your calculation, then it will appear in the Calculated Field window in the formula text area. You can change the drop-down value and see only the Logical functions.

 

calculated field window

 

Step 3: After selecting the Logical function, it lists all the functions available under it’s category. On selecting each function, it describes about what the function does with an example so it would be easy to use the same.

logical function with description

 

Step 4: Now lets have a brief knowledge about the available Logical functions.

1. CASE Function :

CASE expression WHEN value1 THEN return1 WHEN value2THEN return2 ELSE default return END

The CASE function is used to perform a logical test and return values based on the result. CASE functions are often short and simple to write.

Example : CASE [Region] WHEN “West” THEN 1 WHEN “East” THEN 2 ELSE 3 END

This CASE function creates a new field by looking through the country field. When the value “United States” is found, it uses “USA.” When “United Kingdom” is found, it uses “UK.” All other values receive the value “World.”

 

casestatement1

 

2. IF Function :

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

The IF function creates a logical test. IF test is true THEN do y. The test part of the function must be Boolean, either by using a Boolean field in the data source or as the result of an expression using operators or logical comparisons (AND, OR, NOT).

Example : IF [Cost]>[Budget Cost] THEN “Over Budget” ELSE “Under Budget” END

IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END

You can expand the functionality of the IF function with the ELSEIF by adding additional IF-THEN statements i.e. you can recreate your CASE formula using an IF-THEN-ELSEIF statement.

Example : IF [Region] = “West” THEN 1 ELSEIF [Region] = “East” THEN 2 ELSE 3 END

 

ifelse1

 

3. IFNULL Function :

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]

The IFNULL function basically runs a true/false test on whether the value in the tested field is NULL and both the arguments of the function must be of same datatype.

 

ifnull1

 

4. IIF Function :

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

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.

Example : IIF( 7>5, “Seven is greater than five”, “Seven is less than five”)

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.

 

iifstatement1

 

5. ISDATE Function :

ISDATE(string)

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

Example :
ISDATE(“January 1, 2003”) = TRUE
ISDATE(“Jan 1 2003”) = TRUE
ISDATE(“1/1/03”) = TRUE
ISDATE(“Janxx 1 2003”) = FALSE

 

6. ISNULL Function :

ISNULL(expression)

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

Example : ISNULL([Country])

 

7. ZN Function :

ZN(expression)

ZN tests to see if a function is null, and if it is, it will return a value of zero.

Example : ZN( [2014 India Population])

Leave A Reply

Your email address will not be published. Required fields are marked *