Types of Built-In Functions
- Tableau provides several types of built-in functions that are used to manipulate and analyze data in calculated fields.
- Tableau functions help in data transformation, calculation, and analysis.
- These functions are categorized into:-
- Number Functions (Math operations)
- String Functions (Text manipulation)
- Date Functions (Date calculations)
- Logical Functions (Conditions and filtering)
- Aggregate Functions (Summarizing data)
1. Number Functions
- Built-in number functions in Tableau are used to perform calculations or manipulations on numeric data within our visualizations or calculated fields.
- These functions can be applied to measure values or to create new calculated fields.
- These functions can be used directly within calculated fields or as part of calculations in Tableau visualizations to perform various types of analysis and data manipulation on numeric data.
- These functions are used for mathematical calculations on numeric fields.
- The summary of Number Functions in tabular form is –
| Function | Descriptions | Example | Results |
| ABS(number) | Returns the absolute value of a number | ABS(-10) | 10 |
| CEILING(number) | Rounds up to the nearest integer | CEILING(4.3) | 5 |
| FLOOR(number) | Rounds down to the nearest integer | FLOOR(4.7) | 4 |
| ROUND(number, decimals) |
Rounds a number to the specified number of decimal places. |
ROUND(4.678, 2) | 4.68 |
| SQRT(number) | Square root | SQRT(25) | 5 |
| POWER(base, exponent) | Exponentiation | POWER(2,3) | 8 |
| RAND() | Returns a random number between 0 and 1 | RAND() | 0.5432 |
| PI() | Returns the value of π (pi) | PI() | 3.14159 |
| PERCENTILE() |
Returns the value at a specified percentile of a set of values. |
PERCENTILE(Sales, 0.75) | |
|
QUARTILE() |
Returns the value at a specified quartile of a set of values. |
QUARTILE(Profit, 3) | |
|
RANK() |
Assigns a rank to each value in a set, with ties given the same rank. |
RANK(Sales) | |
|
TRUNC() |
Truncates a number to the specified number of decimal places. |
TRUNC(Cost, 2) | |
|
Zn() |
Returns the value if it’s a number; otherwise, returns zero. |
Zn(Profit) | |
|
NTILE() |
Divides a set of values into a specified number of tiles and assigns each value a tile number. |
NTILE(4) |
2. String Functions
- String type in-built functions in Tableau are used to manipulate and perform operations on string values within the visualizations or calculated fields.
- These functions can be applied to dimensions or measure values that are represented as strings.
- These functions can be used directly within calculated fields or as part of calculations in Tableau visualizations to manipulate and analyze string data in various ways.
- These functions are used to manipulate text data.
- The summary of String Functions in tabular forms are –
| Function | Descriptions | Example | Result |
| LEFT(string, n) | Returns the first n characters | LEFT(“Tableau”, 3) | “Tab” |
| RIGHT(string, n) | Returns the last n characters | RIGHT(“Tableau”, 4) | “leau” |
| LEN(string) | Returns the length of the string | LEN(“Data”) | 4 |
| LOWER(string) | Converts to lowercase | LOWER(“HELLO”) | “hello” |
| UPPER(string) | Converts to uppercase | UPPER(“hello”) | “HELLO” |
| TRIM(string) | Removes leading/trailing spaces | TRIM(” Tableau “) | “Tableau” |
| REPLACE(string, old, new) | Replaces substring | REPLACE(“Hello World”, “World”, “Tableau”) | “Hello Tableau” |
| CONTAINS(string, substring) |
Checks whether a string contains a specified substring, returning true or false. |
CONTAINS(“Tableau”, “Tab”) | TRUE |
| STR() or STR() |
Converts a number to a string. |
STR(Sales) | |
| CONCAT() |
Concatenates two or more strings together. |
CONCAT(First Name, ” “, Last Name) | |
| MID() or SUBSTRING() |
Returns a substring from within a string, starting at a specified position and optionally with a specified length. |
MID(Order ID, 4, 3) | |
| PROPER() |
Capitalize the first letter of each word in a string. |
PROPER(Product Name) |
|
| FIND() |
Returns the starting position of a specified substring within a string. |
FIND(“needle”, Haystack) | |
| SPLIT() |
Splits a string into an array of substrings based on a specified delimiter. |
SPLIT(Address, “,”) | |
| LEFTPAD() and RIGHTPAD() |
Pads a string with specified characters on the left or right side to reach a specified length. |
LEFTPAD(Customer ID, 8, “0”) |
3. Date Functions
- These functions are used for date calculations and formatting.
- The summary of Date Functions in tabular form is –
| Function | Descriptions | Example | Result |
| TODAY() | Returns today’s date | TODAY() | 2025-02-04 |
| NOW() | Returns the current date and time | NOW() | 2025-02-04 10:15:00 |
| YEAR(date) | Extracts the year from the date | YEAR(#2024-06-15#) | 2024 |
| MONTH(date) | Extracts month | MONTH(#2024-06-15#) | 6 |
| DAY(date) | Extracts day | DAY(#2024-06-15#) | 15 |
| DATEADD(unit, value, date) | Adds to date | DATEADD(‘month’, 1, #2024-06-15#) | 2024-07-15 |
| DATEDIFF(unit, start, end) | Difference between dates | DATEDIFF(‘day’, #2024-01-01#, #2024-02-01#) | 31 |
4. Logical Functions
- These functions are used to evaluate different conditions.
- The summary of Logical Functions in tabular form is –
| Function | Description | Example | Result |
| IF condition THEN value ELSE value END | Conditional statement | IF [Sales] > 1000 THEN “High” ELSE “Low” END | “High” or “Low” |
| CASE expression WHEN value THEN result END | Case-based condition | CASE [Region] WHEN “East” THEN “E” ELSE “Other” END | “E” or “Other” |
| IFNULL(expression, value) | Returns a value if the expression is NULL | IFNULL([Discount], 0) | Returns 0 if discount is NULL |
| ISNULL(expression) | Checks if the value is NULL | ISNULL([Profit]) | TRUE or FALSE |
| AND | Returns TRUE if both conditions are true | [Sales] > 500 AND [Profit] > 100 | TRUE or FALSE |
| OR | Returns TRUE if any condition is true | [Category] = “Furniture” OR [Category] = “Office Supplies” | TRUE |
5. Aggregate Functions
- These functions are used to perform calculations on multiple rows.
- The summary of Aggregate Functions in tabular form is –
| Function | Descriptions | Example | Result |
| SUM(expression) |
Calculates the sum of a set of values. |
SUM(Sale]) | Total sales |
| AVG(expression) |
Calculates the average of a set of values. |
AVG(Profit) | Average profit |
| MIN(expression) |
Returns the minimum value from a set of values. |
MIN(Discount) | Smallest discount |
| MAX(expression) |
Returns the maximum value from a set of values. |
MAX(Profit) | Highest profit |
| COUNT(expression) |
Returns the number of non-null values in a set of values. |
COUNT(Order ID) | Number of orders |
| COUNTD(expression) |
Returns the number of unique values in a set of values. |
COUNTD(Customer Name) | Unique customers |
| MEDIAN(expression) |
Returns the median value from a set of values. |
MEDIAN(Sales) | Median sales |
|
VAR(expression)/ VARIANCE(expression) |
Returns the variance of a set of values. |
VAR(Profit) | Variance of profit |
|
STDEV(expression)/ STDDEV(expression) |
Returns the standard deviation of a set of values. |
STDEV(Sales) | The standard deviation of sales |
0 Comments