Documentation Analytics Components Guide

Basic functions

Last update: January 16, 2025

Topics:
Calculated Metrics

CREATED FOR:

User

The Calculated metrics builder lets you apply statistical and mathematical functions. This article documents alphabetical list of the functions and their definitions.

NOTE

Where metric is identified as an argument in a function, other expressions of metrics are also allowed. For example, COLUMN MAXIMUM(metrics) also allows for COLUMN MAXIMUM(PageViews + Visits).

Table functions versus row functions

A table function is one where the output is the same for every row of the table. A row function is one where the output is different for every row of the table.

Where applicable and relevant, a function is annotated with the type of function: Table or Row

What does the include-zeros parameter mean?

It tells whether to include zeros in the computation. Sometimes zero means nothing, but sometimes it’s important.

For example, if you have a Revenue metric, and then add a Page Views metric to the report, there are suddenly more rows for your revenue, which are all zero. You probably don’t want that additional metric to affect any MEAN, ROW MINIMUM, QUARTILE, and more calculations that you have in the revenue column. In this case, you would check the include-zeros parameter.

An alternative scenario is that you have two metrics of interest and one has a higher average or minimum because some of the rows are zeros. In that case, you can opt not to check the parameter to include zeros

Absolute Value

ABSOLUTE VALUE(metric)

Row Returns the absolute value of a number. The absolute value of a number is the number with a positive value.

Argument	Description
metric	The metric for which you want to calculate the absolute value.

Column Maximum

COLUMN MAXIMUM(metric, include_zeros)

Returns the largest value in a set of dimension elements for a metric column. MAXV evaluates vertically within a single column (metric) across dimension elements.

Argument	Description
metric	Requires at least one metric but can take any number of metrics as parameters.
include_zeros	Whether or not to include zero values in the calculations.

Column Minimum

COLUMN MINIMUM(metric, include_zeros)

Returns the smallest value in a set of dimension elements for a metric column. MINV evaluates vertically within a single column (metric) across dimension elements.

Argument	Description
metric	Requires at least one metric but can take any number of metrics as parameters.
include_zeros	Whether or not to include zero values in the calculations.

Column Sum

COLUMN SUM(metric)

Adds all numeric values for a metric within a column (across the elements of a dimension).

Argument	Description
metric	Requires at least one metric but can take any number of metrics as parameters.

Count

COUNT(metric)

Table Returns the number, or count, of non-zero values for a metric within a column (the number of unique elements reported within a dimension).

Argument	Description
metric	The metric you want to count.

Exponent

EXPONENT(metric)

Row Returns e raised to the power of a given number. The constant e equals 2.71828182845904, the base of the natural logarithm. EXPONENT is the inverse of LN, the natural logarithm of a number.

Argument	Description
metric	The exponent applied to the base e.

Mean

MEAN(metric, include_zeros)

Table Returns the arithmetic mean, or average, for a metric in a column.

Argument	Description
metric	The metric for which you want to calculate the average.
include_zeros	Whether or not to include zero values in the calculations.

Median

MEDIAN(metric, include_zeros)

Table Returns the median for a metric in a column. The median is the number in the middle of a set of numbers. That is, half the numbers have values that are greater than or equal to the median, and half are less than or equal to the median.

Argument	Description
metric	The metric for which you want to calculate the median.
include_zeros	Whether or not to include zero values in the calculations.

Modulo

MODULO(metric_X, metric_Y)

Returns the remainder after dividing x by y using Euclidean division.

Argument	Description
metric_X	The first metric that you would like to divide.
metric_Y	The second metric that you would like to divide.

Examples

The return value has the same sign as the input (or is zero).

MODULO(4,3) = 1
MODULO(-4,3) = -1
MODULO(-3,3) = 0

To ensure you always get a positive number, use

MODULO(MODULO(x,y)+y,y)

Percentile

PERCENTILE(metric, k, include_zeros)

Table Returns the nth percentile, which is a value between 0 and 100. When n < 0, the function uses zero. When n > 100, the function returns 100.

Argument

Description

metric

The percentile value in the range 0 to 100, inclusive.

k

The metric column that defines relative standing.

include_zeros

Whether or not to include zero values in the calculations.

Power Operator

POWER OPERATOR(metric_X, metrix_Y)

Returns x raised to the y power.

Argument

Description

metric_X

The metric that you would like to raise to the metric_Y power.

metric_Y

The power you would like to raise metric_X to.

Quartile

QUARTILE(metric, quartile, include_zeros)

Table Returns the quartile of values for a metric. For example, quartiles can be used to find the top 25% of products driving the most revenue. COLUMN MINIMUM, MEDIAN, and COLUMN MAXIMUM return the same value as QUARTILE when quartile is equal to 0 (zero), 2, and 4, respectively.

Argument

Description

metric

The metric for which you want to calculate the quartile value.

quartile

Indicates which quartile value to return.

include_zeros

Whether or not to include zero values in the calculations.

Round

ROUND(metric, number)

Round without a number parameter is the same as round with a number parameter of 0, namely round to the nearest integer. With a number parameter, ROUND returns the number digits to the right of the decimal. If number is negative, it returns 0’s to the left of the decimal.

Argument

Description

metric

The metric that you want to round.

number

How many digits to the right of the decimal to return. (If negative returns zeros to the left of the decimal).

Examples

ROUND( 314.15, 0) = 314
ROUND( 314.15, 1) = 314.1
ROUND( 314.15, -1) = 310
ROUND( 314.15, -2) = 300

Row Count

ROW COUNT()

Returns the count of rows for a given column (the number of unique elements reported within a dimension). Uniques exceeded is counted as 1.

Row Max

ROW MAX(metric, include_zeros)

Maximum of the columns of each row.

Argument

Description

metric

Requires at least one metric but can take any number of metrics as parameters.

include_zeros

Whether or not to include zero values in the calculations.

Row Min

ROW MIN(metric, include_zeros)

Minimum of the columns of each row.

Argument

Description

metric

Requires at least one metric but can take any number of metrics as parameters.

include_zeros

Whether or not to include zero values in the calculations.

Row Sum

ROW SUM(metric, include_zeros)

Sum of the columns of each row.

Argument

Description

metric

Requires at least one metric but can take any number of metrics as parameters.

Square Root

SQUARE ROOT(metric, include_zeros)

Row Returns the positive square root of a number. The square root of a number is the value of that number raised to the power of 1/2.

Argument

Description

metric

The metric for which you want to calculate the square root.

Standard Deviation

STANDARD DEVIATION(metric, include_zeros)

Table Returns the standard deviation, or square root of the variance, based on a sample population of data.

Argument

Description

The metric for which you want to calculate the standard deviation.

include_zeros

Whether or not to include zero values in the calculations.

Variance

VARIANCE(metric, include_zeros)

Table Returns the variance based on a sample population of data.

Argument

Description

metric

The metric for which you want to calculate the variance.

include_zeros

Whether or not to include zero values in the calculations.

The equation for VARIANCE is:

Where x is the sample mean, MEAN(metric), and n is the sample size.

To calculate a variance, you look at an entire column of numbers. From that list of numbers you first calculate the average. Once you have the average, you go through each entry and do the following:

Subtract the average from the number.
Square the result.
Add that to the total.

Once you have iterated over the entire column, you have a single total. You then divide that total by the number of items in the column. That number is the variance for the column. It is a single number. It is, however, displayed as a column of numbers.

In the example of the following three-item column:

column

1

2

3

The average of this column is 2. The variance for the column is ((1 - 2)² + (2 - 2)² + (3 - 2)²/3) = 2/3.

recommendation-more-help