Data analysis involves a lot of calculations. In Tableau, the calculation editor has applied calculations to the fields being analyzed. Tableau has multiple inbuilt functions which help in creating expressions for complex calculations.
There is a list of Tableau functions that are categorized into five parts:
- Number functions
- String functions
- Date functions
- Logical functions
- Aggregate functions
1. Number Functions
Number function is a function that uses for the numeric calculations. They take only numbers as inputs.
Let's see some essential examples of number functions:
- Ceiling (Number): It rounds a number to the nearest integer of equal or greater values.
Example: CEILING (4.155) = 5
- Power (Number, Power): It raises the number to the specified power.
Example: POWER (2^3) = 8
- Round (Number, Decimals): It rounds the number to a specified number of digits.
Example: ROUND (5.14522) = 5.14
2. String Functions
String functions are used for the manipulation of the string.
Let's see some essential examples of string functions:
- LEN (String): LEN string returns the length of the string.
Example: LEN ("Tableau") = 7
- LTrim (String): It returns a string that contains a copy of the specified string with no leading (LTrim) or trailing (RTrim) spaces.
Example: LTrim (" Tableau ") = "Tableau"
- REPLACE (String, Substring Replacement): It searches the string for substring and replaces it. If the substring is not found, that string is not changed.
Example: REPLACE ("Green yellow Green", "yellow", "Red") = "Green Red Green"
- UPPER (String): It returns the string with all uppercase characters.
Example: UPPER ("Tableau") = "TABLEAU"
3. Date Functions
Tableau has many date functions, and all the date functions use the date_part, this is the string indicating part of the date such as day, month, or year.
Let's see some essential examples of date functions:
- DATEADD (date_part, increment, date): It's added an increment to the date. The type of increment is specified in the date_part.
Example: DATEADD ('month', 5, #2018-06-15#) = 2018-11-15 01:00:00 AM
- DATENAME (date_part, date, start_of_week): It returns date_part of date as a string. And the start_of_week parameter is optional.
Example: DATENAME ('month', #2018-03-15#) = "March"
- DAY (date): It returns the day of the given date in integer form.
Example: DAY (#2018-04-12#) = 12
- NOW (): It returns the current time and date.
Example: NOW ( ) = 2018-04-15 1:08:21 PM
4. Logical Functions
These functions evaluate some single values and produce a Boolean output.
See some essential examples of logical function:
- IFNULL (expression1, expression2): If the result is not null, then IFNULL function returns the first expression, and if it is null, then it returns the second expression.
Example: IFNULL ([Sales], 0) = [Sales]
- ISDATE (string): If the string argument can be converted to a date, the ISDATE function returns TRUE, and if it cannot, it returns FALSE.
Example: ISDATE ("12/06/99") = "TRUE"
ISDATE ("14/06/99") = "FALSE"
- MIN (expression): The MIN function returns the minimum result for each record.
5. Aggregate Functions
Let's see some essential examples of aggregate functions:
- AVG (expression): It returns the average of all the values in the expression. AVG is used only with numeric fields. And the Null values are ignored.
- COUNT (expression): It returns the number of items in a group. And the Null values are not counted.
- MEDIAN (expression): It returns the median of an expression over all records. Median can only be used with numeric fields, and Null values are ignored.
- STDEV (expression): It returns the statistical standard deviation of all values in the given expression based on a sample of the population.