Tableau - Functions
Any data analysis involves a lot of calculations. In Tableau, the calculation editor is used to apply calculations to the fields being analyzed. Tableau has a number of inbuilt functions which help in creating expressions for complex calculations.
Following are the description of different categories of functions.
- Number Functions
- String Functions
- Date Functions
- Logical Functions
- Aggregate Functions
These are the functions used for numeric calculations. They only take numbers as inputs. Following are some examples of important number functions.
|CEILING (number)||Rounds a number to the nearest integer of equal or greater value.||CEILING(2.145) = 3|
|POWER (number, power)||Raises the number to the specified power.||POWER(5,3) = 125|
|ROUND (number, [decimals])||Rounds the numbers to a specified number of digits.||ROUND(3.14152,2) = 3.14|
String Functions are used for string manipulation. Following are some important string functions with examples
|LEN (string)||Returns the length of the string.||LEN("Tableau") = 7|
|LTRIM (string)||Returns the string with any leading spaces removed.||LTRIM(" Tableau ") = "Tableau"|
|REPLACE (string, substring, replacement)||Searches the string for substring and replaces it with a replacement. If the substring is not found, the string is not changed.||REPLACE("GreenBlueGreen", "Blue", "Red") = "GreenRedGreen"|
|UPPER (string)||Returns string, with all characters uppercase.||UPPER("Tableau") = "TABLEAU"|
Tableau has a variety of date functions to carry out calculations involving dates. All the date functions use the date_part which is a string indicating the part of the date such as - month, day, or year. Following table lists some examples of important date functions.
|DATEADD (date_part, increment, date)||Returns an increment added to the date. The type of increment is specified in date_part.||DATEADD ('month', 3, #2004-04-15#) = 2004-0715 12:00:00 AM|
|DATENAME (date_part, date, [start_of_week])||Returns date_part of date as a string. The start_of_week parameter is optional.||DATENAME('month', #200404-15#) = "April"|
|DAY (date)||Returns the day of the given date as an integer.||DAY(#2004-04-12#) = 12|
|NOW( )||Returns the current date and time.||NOW( ) = 2004-04-15 1:08:21 PM|
These functions evaluate some single value or the result of an expression and produce a boolean output.
|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.||IFNULL([Sales], 0) = [Sales]|
|ISDATE (string)||The ISDATE function returns TRUE if the string argument can be converted to a date, and FALSE if it cannot.||
ISDATE("11/05/98") = TRUE
ISDATE("14/05/98") = FALSE
|MIN(expression)||The MIN function returns the minimum of an expression across all records or the minimum of two expressions for each record.|
|AVG(expression)||Returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored.|
|COUNT (expression)||Returns the number of items in a group. Null values are not counted.|
|MEDIAN (expression)||Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored.|
|STDEV (expression)||Returns the statistical standard deviation of all values in the given expression based on a sample of the population.|