String and Logical functions

Calculations in Tableau

Agata Bak-Geerinck

Senior Data Analyst, Telenet

Overview of data types

Data type - an attribute of a piece of data that tells a computer how to interpret its value.

  • String
  • Date and Time
  • Numeric
  • Boolean (True / False)
  • ...

Structured data sources: one column = one data type.

A table with icons and which data types they represent in Tableau

Calculations in Tableau

String data type

String - a sequence of one or more letters, numbers or other characters.

Examples of string data:

  • "Apple"
  • "M. L. King"
  • "Year 1992"
  • "Female, 29 years old"

In calculations, string values are enclosed in " ".

  • e.g IF [Word] = "Apple" THEN "Fruit" END
Calculations in Tableau

String functions in Tableau

+ Concatenation - joining two or more strings end-to end: A table with an example of concatenation of First and Last name

SPLIT() - extracting parts of a string based on a delimiter: A table with an example of a split of Name field

Calculations in Tableau

String functions in Tableau

LEFT(), RIGHT(), MID() - extract requested, fixed number of characters, based on the position in the string:

A table with an example of Left, Right and Mid functions

Calculations in Tableau

Logical functions: Boolean (True/False)

Logical functions allow to determine if a certain condition is true or false and returns a requested value based on evaluation.

Boolean (True/False) conditions:

  • Top Athlete? : [Low activity ratio] < 0.1 returns "TRUE" or "FALSE"

  • Possible to include several checks in the same condition, adding AND OR statements:

    • ([Gender]="Female" AND [Age] = 30) OR ( [Gender]="Male" AND [Age] = 35 )

  • Negation:

    • [Category] = "Busy Mum" AND NOT [Occupation] = "Clerk"
    • [Category] = "Busy Mum" AND [Occupation] != "Clerk"
    • [Category] = "Busy Mum" AND [Occupation] <> "Clerk"
Calculations in Tableau

Logical functions: IF, IIF, CASE

IF function:

  • IF test1 THEN ____ END
  • IF test1 THEN ____ ELSE ____ END
  • IF test1 THEN ____ ELSEIF ____ THEN ____ ELSE ____ END

IIF function

  • IIF (test, ____ , ____)

CASE function

  • CASE ____ WHEN ____ THEN ____ WHEN ___ THEN ____ ELSE ____ END

IF allows algebra conditions, e.g :

An example of Tableau's IF function syntax

White spacing

IIF() allows algebra conditions, e.g :

An example of Tableau's IIF function syntax

CASE searches for an exact match, e.g :

An example of Tableau's CASE function syntax

Calculations in Tableau

Other logical functions

ISNULL, ISDATE - checks if a value is "Null" or a date:

  • ISNULL([Steps]) returns "TRUE" or "FALSE"
  • ISDATE([Activity Date]) returns "TRUE"

IFNULL - checks if a value is "Null" and controls the output:

  • IFNULL ([Steps], 0)
  • IFNULL ([Steps], "No steps")

ZN - returns a 0 if a value is "Null"

  • ZN([Steps]) returns 0
Calculations in Tableau

Let's practice!

Calculations in Tableau

Preparing Video For Download...