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
- These functions are used for mathematical calculations on numeric fields.
- The summary of Number Functions in tabular forms are –
| Function | Descriptions | Example | Results |
ABS(number) |
Returns absolute value | 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 to given 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 random number between 0 and 1 | RAND() |
0.5432 |
PI() |
Returns value of π (pi) | PI() |
3.14159 |
2. String Functions
- 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 first n characters |
LEFT("Tableau", 3) |
"Tab" |
RIGHT(string, n) |
Returns last n characters |
RIGHT("Tableau", 4) |
"leau" |
LEN(string) |
Returns length of 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 if a string contains a substring | CONTAINS("Tableau", "Tab") |
TRUE |
3. Date Functions
- These functions are used for date calculations and formatting.
- The summary of Date Functions in tabular forms are –
| Function | Descriptions | Example | Result |
TODAY() |
Returns today’s date | TODAY() |
2025-02-04 |
NOW() |
Returns current date and time | NOW() |
2025-02-04 10:15:00 |
YEAR(date) |
Extracts year from 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 forms are –
| 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 value if 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 forms are –
| Function | Descriptions | Example | Result |
SUM(expression) |
Returns total sum | SUM([Sales]) |
Total sales |
AVG(expression) |
Returns average | AVG([Profit]) |
Average profit |
MIN(expression) |
Returns minimum value | MIN([Discount]) |
Smallest discount |
MAX(expression) |
Returns maximum value | MAX([Profit]) |
Highest profit |
COUNT(expression) |
Counts the number of values | COUNT([Order ID]) |
Number of orders |
COUNTD(expression) |
Counts distinct values | COUNTD([Customer Name]) |
Unique customers |
MEDIAN(expression) |
Returns median value | MEDIAN([Sales]) |
Median sales |
VAR(expression) |
Returns variance | VAR([Profit]) |
Variance of profit |
STDEV(expression) |
Returns standard deviation | STDEV([Sales]) |
The standard deviation of sales |
0 Comments