Functions and Operators
Functions and Operators
Expressions are used to perform calculations for:
- Table calculations (which include expressions used in data tests)
- Custom fields
- Custom filters
A major part of these expressions is the functions and operators that you can use in them. The functions and operators can be divided into a few basic categories:
- Mathematical: Number-related functions
- String: Word- and letter-related functions
- Dates: Date- and time-related functions
- Logical transformation: Includes boolean (true or false) functions and comparison operators
- Positional transformation: Retrieving values from different rows or pivots
Some Functions Are Only Available for Table Calculations
Expressions for custom filters and custom fields do not support functions that convert datatypes, aggregate data from multiple rows, or refer to other rows or pivot columns. These functions are supported only for table calculations (including table calculations used in the expression parameter of a data test).
This page is organized to clarify which functions and operators are available, depending on where you are using your expression.
Mathematical Functions and Operators
Mathematical functions and operators work in one of two ways:
- Some mathematical functions perform calculations based on a single row. For example, rounding, adding, multiplying, and similar functions can be used for values in a single row, returning a distinct value for each and every row. All mathematical operators, such as +, are applied one row at a time.
- Other mathematical functions, like averages and running totals, operate over many rows. These functions take many rows and reduce them to a single number, then display that same number on every row.
Functions for Any Expression
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| round | round (value, num_decimals) | Returns value rounded to num_decimals decimal places. For examples using round, see the Using pivot_index in table calculations and Standard Deviation and Simple Time Series Outlier Detection Using Table Calculations Community topics. |
Functions for Table Calculations Only
Warning
Many of these functions operate over many rows and will only consider the rows returned by your report.
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| count | count (expression) | Returns the count of non-null values in the column defined by expression, unless expression defines a column of lists, in which case returns the count in each list. |
| count_distinct | count_distinct (expression) | Returns the count of distinct non-null values in the column defined by expression, unless expression defines a column of lists, in which case returns the count in each list. |
| max | max(expression) | Returns the max of the column created by expression unless expression defines a column of lists, in which case returns the max of each list. For examples using max, see the Using lists in table calculations and Grouping by a dimension in table calculations Community topics. |
| mean | mean(expression) | Returns the mean of the column created by expression unless expression defines a column of lists, in which case returns the mean of each list. For examples using mean, see the Calculating Moving Averages Help Center article and the Standard Deviation and simple time series outlier detection using Table Calculations Community topic. |
| min | min(expression) | Returns the min of the column created by expression unless expression defines a column of lists, in which case returns the min of each list. |
| percent_rank | percent_rank(column, value) | Returns the rank of value in column as a percentage from 0 to 1 inclusive. |
| percentile | percentile (value_column,percentile_value) | Returns the value from the column created by expression corresponding to the given percentile_value, unless expression defines a column of lists, in which case returns the percentile value for each list. percentile_value must be between 0 and 1; otherwise returns null. |
| rank | rank (value, expression) | Returns the rank of value in the column created by expression. |
| rank_avg | rank_avg(value, expression) | Returns the average rank of value in the column created. |
| running_total | running_total(value_column) | Returns a running total of the values in the value_column. |
| sum | sum(expression) | Returns the sum of the column created by expression unless expression defines a column of lists, in which case returns the sum of each list. |
Operators for Any Expression
You can use the following standard mathematical operators:
| OPERATOR | SYNTAX | PURPOSE |
|---|---|---|
| + | value_1 + value_2 | Adds value_1 and value_2. |
| – | value_1 – value_2 | Subtracts value_2 from value_1. |
| * | value_1 * value_2 | Multiplies value_1 and value_2. |
| / | value_1 / value_2 | Divides value_1 by value_2. |
String Functions
String functions operate on sentences, words, or letters, which are collectively called “strings.” You can use string functions to capitalize words and letters, extract parts of a phrase, check to see if a word or letter is in a phrase, or replace elements of a word or phrase. String functions can also be used to format the data returned in the table.
Functions for Any Expression
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| concat | concat (value_1, value_2, …) | Returns value_1, value_2, …, value_n joined as one string. |
| contains | contains (string, search_string) | Returns Yes if string contains search_string, and No otherwise. |
| length | length (string) | Returns the number of characters in string. |
| lower | lower (string) | Returns string with all characters converted to lowercase. |
| position | position (string, search_string) | Returns the start index of search_string in string if it exists, and 0 otherwise. |
| replace | replace (string, old_string, new_string) | Returns string with all occurrences of old_string replaced with new_string. |
| substring | substring (string, start_position, length) | Returns the substring of string beginning at start_position consisting of length characters. |
| upper | upper (string) | Returns string with all characters converted to uppercase. |
Functions for Table Calculations Only
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| to_number | to_number(string) | Returns the number represented by string, or null if the string cannot be converted. |
| to_string | to_string(value) | Returns the string representation of value, or an empty string if value is null. |
Date Functions
Date functions enable you to work with dates and times.
Functions for Any Expression
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| add_days | add_days(number, date) | Adds number days to date. |
| add_months | add_months(number, date) | Adds number months to date. |
| date | date(year, month, day) | Returns “year-month-day” date or null if the date would be invalid. |
| date_time | date_time(year, month, day,hours, minutes, seconds) | Returns “year-month-day hours:minutes:seconds” date or null if the date would be invalid. |
| diff_days | diff_days(start_date, end_date) | Returns the number of days between start_date and end_date. For an example, see the Using dates in table calculations Community topic. |
| diff_months | diff_months(start_date, end_date) | Returns the number of months between start_date and end_date. For an example, see the Grouping by a dimension in table calculations Community topic. |
| extract_days | extract_days(date) | Extracts the days from date. For an example, see the Using dates in table calculations Community topic. |
| extract_months | extract_months(date) | Extracts the months from date. |
| now | now() | Returns the current date and time. For examples using now, see the Now() Table Calculation Function Has Better Timezone Handling and Using dates in table calculations Community topics. |
| trunc_days | trunc_days(date) | Truncates date to days. |
| trunc_months | trunc_months(date) | Truncates date to months. |
Functions for Table Calculations Only
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| to_date | to_date(string) | Returns the date and time corresponding to string (YYYY, YYYY-MM, YYYY-MM-DD, YYYY-MM-DD hh, YYYY-MM-DD hh:mm, or YYYY-MM-DD hh:mm:ss). |
Logical Functions, Operators, and Constants
Logical functions and operators are used to assess whether something is true or false. Expressions using these elements take a value, evaluate it against some criteria, return Yes if the criteria are met, and No if the criteria are not met. There are also various logical operators for comparing values and combining logical expressions.
Functions for Any Expression
| FUNCTION | SYTAX | PURPOSE |
|---|---|---|
| coalesce | coalesce(value_1, value_2, …) | Returns the first non-null value in value_1, value_2, …, value_n if found and null otherwise. For examples using coalesce, see the Creating a running total across rows with table calculations, Creating a percent of total across rows with table calculations, and Using pivot_index in table calculations Community topics. |
| if | if(yesno_expression,value_if_yes, value_if_no) | If yesno_expression evaluates to Yes, returns the value_if_yes value. Otherwise, returns the value_if_no value. For an example, see the Grouping by a dimension in table calculations Community topic. |
| is_null | is_null(value) | Returns Yes if value is null, and No otherwise. For an example, see the Using Table Calculations documentation page. |
Operators for Any Expression
The following comparison operators can be used with any data type:
| OPERATOR | SYNTAX | PURPOSE |
|---|---|---|
| = | value_1 = value_2 | Returns Yes if value_1 is equal to value_2, and No otherwise. |
| != | value_1 != value_2 | Returns Yes if value_1 is not equal to value_2, and No otherwise. |
The following comparison operators only can be used with numbers and dates:
| OPERATOR | SYNTAX | PURPOSE |
|---|---|---|
| > | value_1 > value_2 | Returns Yes if value_1 is greater than value_2, and No otherwise. |
| < | value_1 < value_2 | Returns Yes if value_1 is less than value_2, and No otherwise. |
| >= | value_1 >= value_2 | Returns Yes if value_1 is greater than or equal to value_2, and No otherwise. |
| <= | value_1 <= value_2 | Returns Yes if value_1 is less than or equal to value_2, and No otherwise. |
You also can combine expressions with these logical operators:
| OPERATOR | SYNTAX | PURPOSE |
|---|---|---|
| AND | value_1 AND value_2 | Returns Yes if both value_1 and value_2 are Yes, and No otherwise. |
| OR | value_1 OR value_2 | Returns Yes if either value_1 or value_2 is Yes, and No otherwise. |
| NOT | NOT value | Returns Yes if value is No, and Yes otherwise. |
Warning
These logical operators must be capitalized. Logical operators written in lowercase will not work.
Logical Constants
You can use logical constants in expressions. These constants are always written in lowercase and have the following meanings:
| CONSTANT | MEANING |
|---|---|
| yes | True |
| no | False |
| null | No Value |
Note that the constants yes and no are the special symbols that mean true or false in expressions. In contrast, using quotes such as in “yes” and “no” creates literal strings with those values.
Logical expressions evaluate to true or false without requiring an if function. For example, this:
if(${field} > 100, yes, no)
is equivalent to this:
${field} > 100
You also can use null to indicate no value. For example, you may want to determine if a field is empty, or assign an empty value in a certain situation. This formula returns no value if the field is less than 1, or the value of the field if it is more than 1:
if(${field} < 1, null, ${field})
Combining AND and OR Operators
AND operators are evaluated before OR operators, if you don’t otherwise specify the order with parentheses.
Positional Functions
When creating table calculations, you can use positional transformation functions to extract information about fields in different rows or pivot columns. You can also create lists and retrieve the current row or pivot column index.
Column and Row Totals for Table Calculations Only
If your Explore contains totals, you can reference total values for columns and rows:
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| :total | ${field:total} | Returns the column total of the field. |
| :row_total | ${field:row_total} | Returns the row total of the field. |
Row-Related Functions for Table Calculations Only
Some of these functions use the relative positions of rows, so changing the sort order of the rows affects the results of the functions.
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| index | index(expression, n) | Returns the value of the nth element of the column created by expression, unless expression defines a column of lists, in which case returns the nth element of each list. |
| list | list(value_1, value_2, …) | Creates a list out of the given values. For an example, see the Using lists in table calculations Community topic. |
| lookup | lookup(value, lookup_column, result_column) | Returns the value in result_column that is in the same row as value is in lookup_column. |
| offset | offset(column, row_offset) | Returns the value of row (n + row_offset) in column, where n is the current row number.For examples using offset, see the Calculating Percent of Previous and Percent Change with Table Calculations and Using offset and pivot_offset in Table Calculations Help Center articles. |
| offset_list | offset_list(column, row_offset,num_values) | Returns a list of the num_values values starting at row (n + row_offset) in column, where n is the current row number. For an example, see the Calculating Moving Averages Help Center article. |
| row | row () | Returns the current row number. For an example, see the Transpose Table (Display Measures as Rows) Help Center article. |
Pivot-Related Functions for Table Calculations Only
Some of these functions use the relative positions of pivot columns, so changing the sort order of the pivoted dimension affects the results of those functions.
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| pivot_column | pivot_column () | Returns the index of the current pivot column. |
| pivot_index | pivot_index (expression, pivot_index) | Evaluates expression in the context of the pivot column at position pivot_index (1 for first pivot, 2 second pivot, etc.). Returns null for unpivoted results. For examples using pivot_index, see the Using pivot_index in table calculations and Creating a percent of total across rows with table calculations Community topics. |
| pivot_offset | pivot_offset(pivot_expression, col_offset) | Returns the value of the pivot_expression in position (n + col_offset), where n is the current pivot column position. Returns null for unpivoted results. For examples using pivot_offset, see the Creating a running total across rows with table calculations Community topic and the Calculating Percent of Previous and Percent Change with Table Calculations and Using offset and pivot_offset in Table Calculations Help Center articles. |
| pivot_offset_list | pivot_offset_list (pivot_expression,col_offset, num_values) | Returns a list of the the num_values values in pivot_expression starting at position (n + col_offset), where n is the current pivot index. Returns null for unpivoted results. |
| pivot_row | pivot_row(expression) | Returns the pivoted values of expression as a list. Returns null for unpivoted results. For examples using pivot_row, see the Aggregating Across Rows (Row Totals) in Table Calculations and How to Calculate Percent-of-Total Help Center articles. |
| pivot_where | pivot_where(select_expression, expression) | Returns the value of expression for the pivot column that uniquely satisfies select_expression or null if such a unique column does not exist. |
The specific pivot functions you use determine whether the table calculation is displayed next to each pivoted column, or is displayed as a single column at the end of the table.
Filter Functions for Custom Filters and Custom Fields
Filter functions let you work with filter expressions to return values based on filtered data. Filter functions work in custom filters, filters on custom measures, and custom dimensions, but are not valid in table calculations.
| FUNCTION | SYNTAX | PURPOSE |
|---|---|---|
| matches_filter | matches_filter (field, `filter_expression`) | Returns Yes if the value of the field matches the filter expression, No if not. |
