You are here:
Functions for Calculated Field Formula
Tableau semantics supports various functions in different categories.
Many of these functions are also supported in Tableau. See the Tableau functions documentation for extra details and examples. If there are differences, Tableau Semantics uses what is on this page.
Aggregation Functions
| function | syntax | Description |
|---|---|---|
| AVG | AVG(expression) | Returns the average of all the values in the expression. Null values are ignored. |
| CORR | CORR(expression1, expression2) | Returns the Pearson correlation coefficient of two expressions. |
| COVAR | COVAR(expression1, expression2) | Returns the sample covariance of two expressions. |
| COVARP | COVARP(expression 1, expression2) | Returns the population covariance of two expressions. |
| COUNT | COUNT(expression) | Returns the number of non—distinct items in a group. Null values aren't counted. |
| COUNTD | COUNTD(expression) | Returns the number of distinct items in a group. Null values aren't counted. |
| MEDIAN | MEDIAN(expression) | Returns the median of an expression across all records. Null values are ignored. |
| PERCENTILE | PERCENTILE(expression, number) | Returns the percentile value from the given expression corresponding to the specified <number>. The <number> must be between 0–1 (inclusive) and must be a numeric constant. |
| STDEV | STDEV(expression) | Returns the statistical standard deviation of all values in the given <expression> based on a sample of the population. |
| STDEVP | STDEVP(expression) | Returns the statistical standard deviation of all values in the given <expression> based on a biased population. |
| SUM | SUM(expression) | Returns the sum of all values in the expression. Null values are ignored. |
| VAR | VAR(expression) | Returns the statistical variance of all values in the given <expression> based on a sample of the population. |
| VARP | VARP(expression) | Returns the statistical variance of all values in the given <expression> based on the entire population. |
Date Functions
Note Invalid inputs cause query failures. Inputs that involve nonexistent dates, such as
February 30, don't round to the nearest real date. Instead, the query fails completely in order
to maintain data integrity.
| FunctioN | syntax | Description |
|---|---|---|
| DATE | DATE(EXPRESSION) | Returns a date given a number, string, or date <expression>. |
| DATEADD | DATEADD(date_part, interval, date) | Returns the <date> with the specified number <interval> added to the specified <date_part> of that date. For example, adding 3 months or 12 days to a starting date. |
| DATEDIFF | DATEDIFF(date_part, date1, date2, [start_of_week]) | Returns the number of date parts (weeks, years, and so on) between two dates. |
| DATENAME | DATENAME(date_part, date, [start_of_week]) | Returns <date_part> of <date> as a string. |
| DATEPARSE | DATEPARSE(date_format, date_string) | Returns specifically formatted strings as dates. |
| DATEPART | DATEPART(date_part, date, [start_of_week]) | Returns the name of the specified date part as an integer. |
| DATETIME | DATETIME(expression) | Returns a datetime given a number, string, or date expression. |
| DATETRUNC | DATETRUNC(date_part, date, [start_of_week]) | Takes a specific date and returns a version of that date at the desired specificity. Because every date must have a value for day, month, quarter, and year, DATETRUNC sets the values as the lowest value for each date part up to the date part specified. |
| DAY | ||
| ISDATE | ISDATE(string) | Returns true if a given <string> is a valid date. |
| ISOQUARTER | ISOQUARTER(date) | Returns the ISO8601 week-based quarter of a given <date> as an integer. |
| ISOWEEK | ISOWEEK(date) | Returns the ISO8601 week-based week of a given <date> as an integer. |
| ISOWEEKDAY | ISOWEEKDAY(date) | Returns the ISO8601 week-based weekday of a given <date> as an integer. |
| ISOYEAR | ISOYEAR(date) | Returns the ISO8601 week-based year of a given date as an integer. |
| MAKEDATE | MAKEDATE(year, month, day) | Returns a date value constructed from the specified <year>, <month>, and <day>. |
| MAKEDATETIME | MAKEDATETIME(date, time) | Returns a datetime that combines a <date> and a <time>. The date can be a date, datetime, or a string type. The time must be a datetime. |
| MAKETIME | MAKETIME(hour, minute, second) | Returns a date value constructed from the specified <hour>, <minute>, and <second |
| MONTH | MONTH(date) | Returns the month of the given <date> as an integer. |
| NOW | NOW() | Returns the current local system date and time. |
| QUARTER | QUARTER(date) | Returns the quarter of the given <date> as an integer. |
| TODAY | TODAY() | Returns the current date as a date data type. |
| WEEK | WEEK(date) | Returns the week of the given <date> as an integer. |
| YEAR | YEAR(date) | Returns the year of the given <date> as an integer. |
Logical Functions
| Function | syntax | Description |
|---|---|---|
| CASE | CASE <expression> WHEN <value1> THEN <then1> WHEN <value2> THEN <then2> ... [ELSE <default>] END | Evaluates the expression and compares it to the specified options (<value1>, <value2>, and so on). When a value that matches the expression is encountered, CASE returns the corresponding return. If no match is found, the default is returned. If there is no default and no matching value, Null is returned. |
| ELSE | CASE <expression> WHEN <value1> THEN <then1> WHEN <value2> THEN <then2>... [ELSE <default>] END | An optional piece of an IF or CASE expression used to specify a default value to return if none of the tested expressions are true. |
| ELSEIF | [ELSEIF <test2> THEN <then2>] | Tests a series of expressions and returns the <then> value for the first true <test>. |
| END | Used to close an IF or CASE expression. | |
| IIF | IIF(<test>, <then>, <else>, [<unknown>]) | Checks whether a condition is met (<test>), and returns <then>if the test is true, <else> if the test is false, and an optional value for <unknown> if the test is null. If the optional unknown isn't specified, IIF returns null. |
| IF | IF <test1> THEN <then1> [ELSEIF <test2> THEN <then2>...] [ELSE <default>] END | Tests a series of expressions and returns the <then> value for the first true <test>. |
| IFNULL | Returns <expr1> if it's non-null, otherwise returns <expr2>. | IFNULL(expr1, expr2) |
| IN | <expr1> IN <expr2> | Returns TRUE if any value in <expr1> matches any value in <expr2>. |
| ISNULL | ISNULL(expression) | Returns true if the <expression> is NULL (does not contain valid data). |
| NOT | NOT <expression> | Performs logical negation on an expression. |
| THEN | IF <test1> THEN <then1> [ELSEIF <test2> THEN <then2>...] [ELSE <default>] END | A required part of an IF, ELSEIF, or CASE expression used to define which result to return if a specific value or test is true. |
| WHEN | CASE <expression> WHEN <value1> THEN <then1> WHEN <value2> THEN <then2> ... [ELSE <default>] END | A required part of a CASE expression. Finds the first <value> that matches <expression> and returns the corresponding <then>. |
| ZN | ZN(expression) | Returns <expression> if it isn't null, otherwise returns zero. |
Logical Operators
| operator | Description |
|---|---|
| = and == | Evaluates if two values are equivalent. The = and == operators are interchangeable. |
| <> and != | Evaluates if two values aren’t equivalent. |
| < | Evaluates if a value is less than the value that follows this symbol. |
| > | Evaluates if a value is less than the value that follows this symbol. |
| <= | Evaluates if a value is less than or equal to the value that follows this symbol. |
| >= | Evaluates if a value is greater than or equal to the value that follows this symbol. |
| AND | Evaluates if two values or expressions are both true. |
| OR | Evaluates if at least one of multiple values or expressions is true. |
Number Functions
| OPerator/function | syntax | Description |
|---|---|---|
| + | The + operator means addition when applied to numbers and means concatenation when applied to strings. You can use it to add a number of days to a date. | |
| — | The — operator means subtraction when applied to numbers and means negation if applied to an expression. You can use it to subtract a number of days from a date or to calculate the difference in days between two dates. | |
| * | The * operator means numeric multiplication. | |
| / | The / operator means numeric division. | |
| () | Specifies that the expressions within the parentheses are evaluated first. All other expressions are evaluated using standard operator precedence. | |
| ABS | ABS(number) | Returns the absolute value of the given <number> |
| ACOS | ACOS(number) | Returns the arccosine (angle) of the given <number>. Invalid arguments cause the query to fail. |
| ASIN | ASIN(number) | Returns the arcsine (angle) of a given <number>. Invalid arguments cause the query to fail. |
| ATAN | ATAN(number) | Returns the arctangent (angle) of a given <number>. |
| ATAN2 | ATAN2(y number, x number) | Returns the arctangent (angle) between two numbers (x and y). The result is in radians. |
| CEILING | CEILING(number) | Rounds a <number> to the nearest integer of equal or greater value. |
| COS | COS(number) | Returns the cosine of an angle. |
| COT | COT(number) | Returns the cotangent of an angle. Invalid arguments cause the query to fail. |
| DEGREES | DEGREES(number) | Converts an angle in radians to degrees. |
| DIV | DIV(integer1, integer2) | Returns the integer part of a division operation, in which <integer1> is divided by <integer2>. |
| EXP | EXP(number) | Returns e raised to the power of the given <number>. |
| FLOAT | FLOAT(expression) | Casts its argument as a floating point number. |
| FLOOR | FLOOR(number) | Rounds a number to the nearest <number> of equal or lesser value. |
| INT | INT(expression) | Casts its argument as an integer. |
| LOG | LOG(number, [base]) | Returns the logarithm of a <number> for the given <base>. If the optional base argument isn't present, base 10 is used. Invalid arguments cause the query to fail. |
| LN | LN(number) | Returns the natural logarithm of a <number>. Invalid arguments cause the query to fail. |
| MAX | MAX(expression) or MAX(expr1, expr2) | Returns the maximum of the two arguments, which must be of the same data type. You can also apply MAX to a single field as an aggregation. |
| MIN | MIN(expression) or MIN(expr1, expr2) | Returns the maximum of the two arguments, which must be of the same data type. You can also apply MIN to a single field as an aggregation. |
| PI | PI() | Returns the numeric constant pi: 3.14159... |
| ROUND | ROUND(number, [decimals]) | Rounds <number> to a specified number of digits. The optional decimals argument specifies how many decimal points of precision to include. If decimals are omitted, the number is rounded to the nearest integer. |
| POWER | POWER(number, power) | Raises the <number> to the specified <power>. |
| RANDOM | RANDOM() | Returns a random decimal number in the following range 0.0 <= x < 1.0. |
| RADIANS | RADIANS(number) | Converts the given <number> from degrees to radians. |
| ROUND | ROUND(number, [decimals]) | Rounds <number> to a specified number of digits. The optional decimals argument specifies how many decimal points of precision to include. If decimals are omitted, the number is rounded to the nearest integer. |
| SIGN | SIGN(number) | Returns the sign of a <number>: The possible return values are -1 if the number is negative, 0 if the number is zero, or 1 if the number is positive. |
| SIN | SIN(number) | Returns the sine of an angle. |
| SQUARE | SQUARE(number) | Returns the square of a <number>. |
| TAN | TAN(number) | Returns the tangent of an angle. |
Text Functions
Note REGEX expressions use the regex library [RE2](https://github.com/google/re2/wiki/Syntax).
In RE2, an empty pattern is considered a match at every possible position (including the start
of the string). Some syntax is unsupported, including backreferences, lookahead, lookbehind, and
possessive quantifiers.
| Function | syntax | Description |
|---|---|---|
| ASCII | ASCII(string) | Returns the ASCII code for the first character of a <string>. |
| CONTAINS | CONTAINS(string, substring) | Compares two arguments of text and returns TRUE if the first argument contains the second argument. If not, returns FALSE. |
| ENDSWITH | ENDSWITH(string, substring) | Returns true if the given <string> ends with the specified <substring>. Trailing white spaces are ignored. |
| FIND | FIND(string, substring, [start]) | Returns the index position of substring in string, or 0 if the substring isn't found. The first character in the string is position 1. If the optional numeric argument start is added, the function ignores any instances of substring that appear before the starting position. |
| FINDNTH | FINDNTH(string, substring, occurrence) | Returns the position of the nth occurrence of substring within the specified string, where n is the occurrence argument. |
| LEFT | LEFT(string, number) | Returns the left-most <number> of characters in the string. |
| LEN | LEN(string) | Returns the length of the string. |
| LOWER | LOWER(string) | Returns the provided <string> in lowercase characters. |
| LTRIM | LTRIM(string) | Returns the provided <string> with any leading spaces removed. |
| MID | MID(string, start, [length]) | Returns a string starting at the specified start position. The first character in the string is position 1. If the optional numeric argument length is added, the returned string includes only that number of characters. |
| PROPER | PROPER(string) | Returns the provided <string> with the first letter of each word is capitalized and the remaining letters are in lowercase. |
| REGEXP_EXTRACT | REGEXP_EXTRACT(string, pattern) | Returns the portion of the string that matches the regular expression pattern. |
| REGEXP_EXTRACT_NTH | REGEXP_EXTRACT_NTH(string, pattern, index) | Returns the portion of the string that matches the regular expression pattern. The substring is matched to the nth capturing group, where n is the given index. If index is 0, the entire string is returned. |
| REGEXP_MATCH | REGEXP_MATCH(string, pattern) | Returns true if a substring of the specified string matches the regular expression pattern. |
| REGEXP_REPLACE | REGEXP_REPLACE(string, pattern, replacement) | Returns a copy of the given string where the regular expression pattern is replaced by the replacement string. |
| REPLACE | REPLACE(string, substring, replacement) | Searches <string> for <substring> and replaces it with <replacement>. If <substring> isn’t found, the string isn’t changed. |
| RIGHT | RIGHT(string, number) | Returns the right-most <number> of characters in the string. |
| RTRIM | RTRIM(string) | Returns the provided <string> with any trailing spaces removed. |
| SPACE | SPACE(number) | Returns a string that is composed of the specified number of repeated spaces. |
| SPLIT | SPLIT(string, delimiter, token number) | Returns a substring from a string using a delimiter character to divide the string into a sequence of tokens. |
| STARTSWITH | STARTSWITH(string, substring) | Returns true if string starts with substring. Leading white spaces are ignored. |
| STR | STR(expression) | Casts its argument as a string. |
| TRIM | TRIM(string) | Returns the provided <string> with leading and trailing spaces removed. |
| UPPER | UPPER(string) | Returns the provided <string> in uppercase characters. |
User Functions
| Function | syntax | Description |
|---|---|---|
| USERID15 | USERID15() | Returns the case-sensitive 15-character Salesforce ID for the logged-in user. |
| USERID18 | USERID18() | Returns the case-sensitive 18-character Salesforce ID for the logged-in user. |
- Use the DATEPARSE Function
The DATEPARSE function takes that string input and the user-specified format and returns a date field.
Did this article solve your issue?
Let us know so we can improve!
