Table calculations and custom filters use Analytics Expressions.A major part of these expressions are the functions and operators that you can use in them. This page includes information about all of these functions and operators.

- Basic Categories
- Some Functions Are Only Available for Table Calculations
- Mathematical Functions and Operators
- String Functions
- Date Functions
- Logical Functions, Operators and Constants
- Positional Functions
- Filter Functions for Custom Filters and Custom Fields

# Basic Categories

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

Analytics expressions for custom filters can use *most *functions and operators while table calculations as well as custom fields can use *any* function or operator. This page is organized to make it clear which functions and operators are available, depending on where you are using your Analytics expression.

The functions that are available *only* for table calculations are typically functions that convert datatypes, aggregate data from multiple rows, or refer to other rows or pivot columns.

# 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, taking a square root, 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 Analytics Expression

Function | Syntax | Purpose |
---|---|---|

abs | `abs(value)` | Returns the absolute value of `value` |

ceiling | `ceiling(value)` | Returns the smallest integer greater than or equal to `value` |

exp | `exp(value)` | Returns e to the power of `value` |

floor | `floor(value)` | Returns the largest integer less than or equal to `value` |

ln | `ln(value)` | Returns the natural logarithm of `value` |

log | `log(value)` | Returns the base 10 logarithm of `value` |

mod | `mod(value, divisor)` | Returns the remainder of dividing `value` by `divisor` |

power | `power(base, exponent)` | Returns `base` raised to the power of `exponent` |

rand | `rand()` | Returns a random number between 0 and 1 |

round | `round(value, num_decimals)` | Returns `value` rounded to `num_decimals` decimal places |

sqrt | `sqrt(value)` | Returns the square root of `value` |

## Functions for Table Calculations Only

Many of these functions operate over many rows and will only consider the rows returned by your query.

Function | Syntax | Purpose |
---|---|---|

acos | `acos(value)` | Returns the inverse cosine of `value` |

asin | `asin(value)` | Returns the inverse sine of `value` |

atan | `atan(value)` | Returns the inverse tangent of `value` |

beta_dist | `beta_dist(value, alpha,` `beta, cumulative)` | Returns the position of `value` on the beta distribution with parameters `alpha` and `beta` . If `cumulative = yes` , returns the cumulative probability |

beta_inv | `beta_inv(probability,` `alpha, beta)` | Returns the position of `probability` on the inverse cumulative beta distribution with parameters `alpha` and `beta` |

binom_dist | `binom_dist(num_successes,` `num_tests,` `probability, cumulative)` | Returns the probability of getting `num_successes` successes in `num_tests` tests with the given `probability` of success. If `cumulative = yes` , returns the cumulative probability |

binom_inv | `binom_inv(num_tests,` `test_probability,` `target_probability)` | Returns the smallest number `k` such that `binom(k, num_tests,` `test_probability, yes)` `>= target_probability` |

chisq_dist | `chisq_dist(value, dof,` `cumulative)` | Returns the position of `value` on the gamma distribution with `dof` degrees of freedom. If `cumulative = yes` , returns the cumulative probability |

chisq_inv | `chisq_inv(probability, dof)` | Returns the position of `probability` on the inverse cumulative gamma distribution with `dof` degrees of freedom |

chisq_test | `chisq_test(actual,` `expected)` | Returns the probability for the chi-squared test for independence between `actual` and `expected` data. `actual` can be a column or a column of lists, and `expected` must be the same type. |

combin | `combin(set_size,` `selection_size)` | Returns the number of ways of choosing `selection_size` elements from a set of size `set_size` |

confidence_norm | `confidence_norm(alpha,` `stdev, n)` | Returns half the width of the normal confidence interval at significance level `alpha` , standard deviation `stdev` , and sample size `n` |

confidence_t | `confidence_t(alpha,` `stdev, n)` | Returns half the width of the Student’s t-distribution confidence interval at significance level `alpha` , standard deviation `stdev` , and sample size `n` |

correl | `correl(column_1, column_2)` | Returns the correlation coefficient of `column_1` and `column_2` |

cos | `cos(value)` | Returns the cosine of `value` |

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 |

covar_pop | `covar_pop(column_1,` `column_2)` | Returns the population covariance of `column_1` and `column_2` |

covar_samp | `covar_samp(column_1,` `column_2)` | Returns the sample covariance of `column_1` and `column_2` |

degrees | `degrees(value)` | Converts `value` from radians to degrees |

expon_dist | `expon_dist(value, lambda,` `cumulative)` | Returns the position of `value` on the exponential distribution with parameter `lambda` . If `cumulative = yes` , returns the cumulative probability |

f_dist | `f_dist(value, dof_1,` `dof_2, cumulative)` | Returns the position of `value` on the F distribution with parameters `dof_1` and `dof_2` . If `cumulative = yes` , returns the cumulative probability |

f_inv | `f_inv(probability, dof_1,` `dof_2)` | Returns the position of `probability` on the inverse cumulative F distribution with parameters `dof_1` and `dof_2` |

fact | `fact(value)` | Returns the factorial of `value` |

gamma_dist | `gamma_dist(value, alpha,` `beta, cumulative)` | Returns the position of `value` on the gamma distribution with parameters `alpha` and `beta` . If `cumulative = yes` , returns the cumulative probability |

gamma_inv | `gamma_inv(probability,` `alpha, beta)` | Returns the position of `probability` on the inverse cumulative gamma distribution with parameters `alpha` and `beta` |

geomean | `geomean(expression)` | Returns the geometric mean of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the geometric mean of each List |

hypgeom_dist | `hypgeom_dist` `(sample_successes,` `sample_size,` `population_successes,` `population_size,` `cumulative)` | Returns the probability of getting `sample_successes` from the given `sample_size` , number of `population_successes` , and `population_size` . If `cumulative = yes` , returns the cumulative probability |

intercept | `intercept(y_column,` `x_column)` | Returns the intercept of the linear regression line through the points determined by `y_column` and `x_column` |

kurtosis | `kurtosis(expression)` | Returns the sample excess kurtosis of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the sample excess kurtosis of each List |

large | `large(expression, k)` | Returns the `k` th largest value of the column created by `expression` unless `expression` defines a column of Lists, in which case returnsthe `k` th largest value of each List |

match | `match(value, expression)` | Returns the row number of the first occurence of `value` in the column created by `expression` unless `expression` defines a column of Lists, in which case returns the position of `value` 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 |

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 |

median | `median(expression)` | Returns the median of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the median of each List |

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 |

mode | `mode(expression)` | Returns the mode of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the mode of each List |

multinomial | `multinomial(value_1,` `value_2, ...)` | Returns the factorial of the sum of the arguments divided by the product of each of their factorials |

negbinom_dist | `negbinom_dist(num_failures,` `num_successes,` `probability,` `cumulative)` | Returns the probability of getting `num_failures` failures before getting `num_successes` successes, with the given `probability` of success. If `cumulative = yes` , returns the cumulative probability |

norm_dist | `norm_dist(value, mean,` `stdev, cumulative)` | Returns the position of `value` on the normal distribution with the given `mean` and `stdev` . If `cumulative = yes` , then returns the cumulative probability |

norm_inv | `norm_inv(probability, mean,` `stdev)` | Returns the position of `probability` on the inverse normal cumulative distribution |

norm_s_dist | `norm_s_dist(value,` `cumulative)` | Returns the position of `value` on the standard normal distribution. If `cumulative = yes` , returns the cumulative probability |

norm_s_inv | `norm_s_inv(probability)` | Returns the position of `probability` on the inverse standard normal cumulative distribution |

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. Note: `percentile_value` must be between 0 and 1, else this returns `null` |

pi | `pi()` | Returns the value of pi |

poisson_dist | `poisson_dist(value, lambda,` `cumulative)` | Returns the position of `value` on the poisson distribution with parameter `lambda` . If `cumulative = yes` , returns the cumulative probability |

product | `product(expression)` | Returns the product of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the product of each List |

radians | `radians(value)` | Converts `value` from degrees to radians |

rank | `rank(value, expression)` | Returns the rank of `value` in the column created by `expression` unless `expression` defines a column of Lists, in which case returns the rank of `value` in each List |

rank_avg | `rank_avg(value, expression)` | Returns the average rank of `value` in the column created by `expression` unless `expression` defines a column of Lists, in which case returns the average rank of `value` in each List |

running_product | `running_product` `(value_column)` | Returns a running product of the values in `value_column` |

running_total | `running_total(value_column)` | Returns a running total of the values in `value_column` |

sin | `sin(value)` | Returns the sine of `value` |

skew | `skew(expression)` | Returns the sample skewness of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the sample skewness of each List |

slope | `slope(y_column, x_column)` | Returns the slope of the linear regression line through points determined by `y_column` and `x_column` |

small | `small(expression, k)` | Returns the `k` th smallest value of the column created by `expression` unless `expression` defines a column of Lists, in which case returnsthe `k` th smallest value of each List |

stddev_pop | `stddev_pop(expression)` | Returns the standard deviation (population) of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the standard deviation (population) of each List |

stddev_samp | `stddev_pop(expression)` | Returns the standard deviation (sample) of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the standard deviation (sample) of each List |

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 |

t_dist | `t_dist(value, dof,` `cumulative)` | Returns the position of `value` on the Student’s t-distribution with `dof` degrees of freedeom. If `cumulative = yes` , returns the cumulative probability |

t_inv | `t_inv(probability, dof)` | Returns the position of `probability` on the inverse normal cumulative distribution with `dof` degrees of freedom |

t_test | `t_test(column_1, column_2,` `tails, type)` | Returns the result of a Student’s t-test on the data from `column_1` and `column_2` , using 1 or 2 `tails` . `type` : 1 = paired, 2 = homoscedastic, 3 = heteroscedastic |

tan | `tan(value)` | Returns the tangent of `value` |

var_pop | `var_pop(expression)` | Returns the variance (population) of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the variance (population) of each List |

var_samp | `var_pop(expression)` | Returns the variance (sample) of the column created by `expression` unless `expression` defines a column of Lists, in which case returns the variance (sample) of each List |

weibull_dist | `weibull_dist(value, shape,` `scale, cumulative)` | Returns the position of `value` on the Weibull distribution with parameters `shape` and `scale` . If `cumulative = yes` , returns the cumulative probability |

z_test | `z_test(data, value, stdev)` | Returns the one-tailed p-value of the z-test using the existing `data` and `stdev` on the hypothesized mean `value` . |

## Operators for Any Analytics Expression

You can use the following standard mathematical operators:

Operators | 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. They can also be used to format the data returned in the table.

## Functions For Any Analytics 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 lower case |

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 upper case |

## 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)` | ADDED5.16 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 Analytics Expression

Function | Syntax | Purpose |
---|---|---|

add_days | `add_days(number, date)` | Adds `number` days to `date` |

add_hours | `add_hours(number, date)` | Adds `number` hours to `date` |

add_minutes | `add_minutes(number, date)` | Adds `number` minutes to `date` |

add_months | `add_months(number, date)` | Adds `number` months to `date` |

add_seconds | `add_seconds(number, date)` | Adds `number` seconds to `date` |

add_years | `add_years(number, date)` | Adds `number` years 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` |

diff_hours | `diff_hours(start_date, end_date)` | Returns the number of hours between `start_date` and `end_date` |

diff_minutes | `diff_minutes(start_date, end_date)` | Returns the number of minutes between `start_date` and `end_date` |

diff_months | `diff_months(start_date, end_date)` | Returns the number of months between `start_date` and `end_date` |

diff_seconds | `diff_seconds(start_date, end_date)` | Returns the number of seconds between `start_date` and `end_date` |

diff_years | `diff_years(start_date, end_date)` | Returns the number of years between `start_date` and `end_date` |

extract_days | `extract_days(date)` | Extracts the days from `date` |

extract_hours | `extract_hours(date)` | Extracts the hours from `date` |

extract_minutes | `extract_minutes(date)` | Extracts the minutes from `date` |

extract_months | `extract_months(date)` | Extracts the months from `date` |

extract_seconds | `extract_seconds(date)` | Extracts the seconds from `date` |

extract_years | `extract_years(date)` | Extracts the years from `date` |

now | `now()` | Returns the current date and time |

trunc_days | `trunc_days(date)` | Truncates `date` to days |

trunc_hours | `trunc_hours(date)` | Truncates `date` to hours |

trunc_minutes | `trunc_minutes(date)` | Truncates `date` to minutes |

trunc_months | `trunc_months(date)` | Truncates `date` to months |

trunc_years | `trunc_years(date)` | Truncates `date` to years |

## 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 deal with whether or not something is true or false. This type of function takes the value of something, evaluates it against some criteria, returns true if the criteria is met, and false if the criteria is not met. There are also various logical operators for comparing values and combining logical expressions.

## Functions For Any Analytics Expression

Function | Syntax | 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 |

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 |

is_null | `is_null(value)` | Returns `Yes` if `value` is `null` , and `No` otherwise |

## Operators For Any Analytics Expression

The following comparison operators can be used with any datatype:

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 Analytics 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 |

## Logical Constants

You can use logical constants in Analytics Expressions. These constants are always written in lowercase and have the following meanings:

Constant | Meaning |
---|---|

`yes` | True |

`no` | False |

`null` | There is no value |

Note that the constants `yes`

and `no`

, are the special symbols that *mean* true or false in Analytics 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. Thus the following expression without additional parentheses:

```
if (
${order_items.days_to_process}>=4 OR
${order_items.shipping_time}>5 AND
${order_facts.is_first_purchase},
"review", "okay")
```

would be evaluated as:

```
if (
${order_items.days_to_process}>=4 OR
(${order_items.shipping_time}>5 AND ${order_facts.is_first_purchase}),
"review", "okay")
```

# Positional Functions

When creating table calculations, you can use positional transformation functions to extract information about fields in different rows or pivot columns.

## 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

Function | Syntax | Purpose |
---|---|---|

index | `index(expression, n)` | Returns the value of the `n` th element of the column created by `expression` , unless `expression` defines a column of Lists, in which case returns the `n` th element of each list |

list | `list(value_1, value_2, ...)` | Creates a List out of the given values |

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 |

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 |

row | `row()` | Returns the current row number |

## 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 |

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 |

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. |

pivot_where | `pivot_where(select_expression, expression)` | Returns the value of `expression` for the pivot column which 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`)` | ADDED5.16 Returns `Yes` if the value of the field matches the filter expression, `No` if not. |

## Matches_filter

### Syntax

`matches_filter(field, `filter expression`)`

The `matches_filter`

function applies the filter expression to the field and returns `Yes`

if the value in the field matches the filter expression or `No`

if it does not.

### Examples

This example returns `Yes`

in a custom field if the invoice date is less than 30 days old:

matches_filter(${billing.invoice_date}, `30 days`)

Use the `if`

function with `matches_filter`

to return different values. The next example shows syntax of a custom field that returns “Late” if the invoice date is over 30 days old:

if(matches_filter(${billing.invoice_date}, `30 days`), "Current", "Late")

### Things to Know

The string that defines the filter expression must be enclosed in backtick (`) characters.