You can use functions and operators in expressions to perform calculations in table functions, custom fields, and custom filters.
Mathematical and statistical functions
| Function | Syntax | Description |
|---|---|---|
| abs | abs(value)
|
Returns the absolute value of
value.
|
| ceiling | ceiling(value)
|
Returns the smallest integer created 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.
|
The following functions are for table calculations only. Many of these functions operate over many rows and will only consider rows returned by your query.
| Function | Syntax | Description |
|---|---|---|
| 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
|
| 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
kth largest value of the column created by
expression unless
expression defines a column of lists, in which
case returns the
kth largest value of each list.
|
| match | match(value, expression)
|
Returns the row number of the first
occurrence 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. |
| netbinom_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, 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,
where column is the column, field, list, or range containing the dataset to
consider; and
value is the column with the value for which
the percentage rank will be determined.
|
| 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.
|
| 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. For example, if you want to rank
orders by their total sale price, you could use
rank( ${order_ items. total_ sale_ price}, ${order_
items. total_ sale_ price}), which gives a rank for each value of
order_ items. total_ sale_ price in your query
when comparing it to the entire column of
order_ items. total_ sale_ price in your
query. In the case where the expression defines multiple lists, this function
returns the relative size of the
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
|
| 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_samp(exprssion)
|
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 freedom. 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_samp(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.
|
Mathematical operators
| Operator | Syntax | Description |
|---|---|---|
| + | 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.
|
Date functions
| Function | Syntax | Description |
|---|---|---|
| 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, second) | 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) | Extract the days from
date.
|
| extract_hours | extract_hours(date) | Extract the hours from
date.
|
| extract_minutes | extract_minutes(date) | Extract the minutes from
date.
|
| extract_months | extract_months(date) | Extract the months from
date.
|
| extract_seconds | extract_seconds(date) | Extract the seconds from
date.
|
| extract_years | extract_years(date) | Extract the years from
date.
|
| now | now() | Returns the current date and time. |
| trunc_days | trunc_days(date) | Truncate
date to days.
|
| trunc_hours | trunc_hours(date) | Truncate
date to hours.
|
| trunc_minutes | trunc_minutes(date) | Truncate
date to minutes.
|
| trunc_months | trunc_months(date) | Truncate
date to months.
|
| trunc_years | trunc_years(date) | Truncate
date to years.
|
The following functions are for table calculations only.
| Function | Syntax | Description |
|---|---|---|
| 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). |
String functions
| Function | Syntax | Description |
|---|---|---|
| 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 the 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.
|
| split | split(string, delimiter)
|
Returns a list of string in
string broken up by
delimiter.
|
| substring | substring(string, start_position,
length)
|
Returns the substring of
string, beginning at
start_ position, consisting of
length characters. The
start_ position starts at 1, with1 indicating
the first character in the string, 2 indicating the second character in the
string, and so on.
|
| 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.
|
| upper | upper(string)
|
Returns
string with all characters converted to
uppercase.
|
The following functions are for table calculations only.
| Function | Syntax | Description |
|---|---|---|
| split | split(string, delimiter)
|
Returns a list of string in
string broken up by
delimiter.
|
| 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.
|
Logical functions, operators, and constants
| Function | Syntax | Description |
|---|---|---|
| case | case(when(yesno_arg, value_if_yes),
when(yesno_arg, value_if_yes), ..., else_value)
|
Allows conditional logic with multiple
conditions and outcomes. Returns
value_if_yes for the first when case whose
yesno_arg value is yes. Returns
else_value if none of the when cases evaluate to yes.
|
| coalesce | coalesce(value_1, value_2,
...)
|
Returns the first non-null value in ,
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.
|
These comparison operators can be used with any data type:
| Operator | Syntax | Description |
|---|---|---|
| = | 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.
|
These comparison operators 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.
|
These logical operators can be used to combine expressions:
| 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 No otherwise. |
Note: Logical operators must be capitalized. Logical operators written in
lowercase will not work.
Column and row totals for table calculations
If you Explore contains totals, you can reference total values for columns and rows:
| Function | Syntax | Description |
|---|---|---|
| :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
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 | Description |
|---|---|---|
| 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. |
| 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 functions for table calculations
| 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 for 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
num_values in
pivot_expression starting at (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 that uniquely
satisfies
select_expression or null if such a unique
column does not exist.
|
Note:
- Some pivot functions use the relative positions of pivot columns, so changing the sort order of the pivoted dimension affects the results of those functions.
- 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 function for custom filters and custom fields
The
matches_filter function lets you work with filter
expressions to return values based on filtered data. This function works on
custom filters, filters on custom measures, and custom dimensions, but is 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.
|