Advanced functions

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

Access these functions by selecting Show all below Functions list in the Components panel. Scroll down to see the list of Advanced functions.

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.

And

AND(logical_test)

Conjunction. Not equal to zero is considered to be true and equals zero is considered to be false. The output is either a 0 (false) or 1 (true).

Argument	Description
logical_test	Requires at least one parameter, but can take any number of parameters. Any value or expression that can be evaluated to TRUE or FALSE

Approximate Count Distinct

APPROXIMATE COUNT DISTINCT(dimension)

Returns the approximated distinct count of dimension items for the selected dimension.

Argument	Description
dimension	The dimension for which you want to calculate the approximated distinct item count

Example

A common use case for this function is when you want to get an approximate number of customers.

Arc Cosine

ARC COSINE(metric)

Row Returns the arccosine, or inverse of the cosine, of a metric. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. If you want to convert the result from radians to degrees, multiply it by 180/PI( ).

Argument	Description
metric	The cosine of the angle you want from -1 to 1

Arc Sine

ARC SINE(metric)

Row Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is a number. The returned angle is given in radians in the range -pi/2 to pi/2. To express the arcsine in degrees, multiply the result by 180/PI( ).

Argument	Description
metric	The sine of the angle you want from -1 to 1

Arc Tangent

ARC TANGENT(metric)

Row Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is a number. The returned angle is given in radians in the range -pi/2 to pi/2. To express the arctangent in degrees, multiply the result by 180/PI( ).

Argument	Description
metric	The tangent of the angle you want from -1 to 1

Cdf-T

CDF-T(metric, number)

Returns the probability that a random variable with student-t distribution with n degrees of freedom have a z-score less than col.

Argument	Description
metric	The metric for which you would like the Cumulative Distribution Function of the student t-distribution
number	The degrees of freedom for the Cumulative Distribution Function of the student t-distribution

Example

CDF-T(-∞, n) = 0
CDF-T(∞, n) = 1
CDF-T(3, 5) ? 0.99865
CDF-T(-2, 7) ? 0.0227501
CDF-T(x, ∞) ? cdf_z(x)

Cdf-Z

CDF-Z(metric, number)

Returns the probability that a random variable with a normal distribution has a z-score less than col.

Argument	Description
metric	The metric for which you would like the Cumulative Distribution Function of the Standard Normal Distribution

Examples

CDF-Z(-∞) = 0
CDF-Z(∞) = 1
CDF-Z(0) = 0.5
CDF-Z(2) ? 0.97725
CDF-Z(-3) ? 0.0013499

Ceiling

CEILING(metric)

Row Returns the smallest integer not less than a given value. For example, if you want to avoid reporting currency decimals for revenue and a product has $569.34, use the formula CEILING(Revenue) to round revenue up to the nearest dollar, or $570.

Argument	Description
metric	The metric that you want to round

Confidence (Lower)

CONFIDENCE(normalizing-container, success-metric, control, significance-treshold)

Calculate the any-time-valid confidence lower using the WASKR method as described in Time-uniform central limit theory and asymptotic confidence sequences.

Confidence is a probabilistic measure of how much evidence there is that a given variant is the same as the control variant. A higher confidence indicates less evidence for the assumption that control and non-control variant have equal performance.

Argument	Description
normalizing-container	The basis (People, Sessions, or Events) on which a test is run.
success-metric	The metric or metrics that a user is comparing variants with.
control	The variant that all other variants in the experiment are being compared with. Enter the name of the control variant dimension item.
significance-threshold	The threshold in this function is set to a default of 95%.

Confidence (Upper)

CONFIDENCE(normalizing-container, success-metric, control, significance-treshold)

Calculate the any-time-valid confidence upper using the WASKR method as described in Time-uniform central limit theory and asymptotic confidence sequences.

Confidence is a probabilistic measure of how much evidence there is that a given variant is the same as the control variant. A higher confidence indicates less evidence for the assumption that control and non-control variant have equal performance.

Argument	Description
normalizing-container	The basis (People, Sessions, or Events) on which a test is run.
success-metric	The metric or metrics that a user is comparing variants with.
control	The variant that all other variants in the experiment are being compared with. Enter the name of the control variant dimension item.
significance-threshold	The threshold in this function is set to a default of 95%.

Cosine

COSINE(metric)

Row Returns the cosine of the given angle. If the angle is in degrees, multiply the angle by PI( )/180.

Argument	Description
metric	The angle in radians for which you want the cosine

Cube Root

CUBE ROOT(metric)

Returns the positive cube root of a number. The cube root of a number is the value of that number raised to the power of 1/3.

Argument	Description
metric	The metric for which you want to calculate the cube root

Cumulative

CUMULATIVE(number, metric)

Returns the sum of the last n elements of column x. If n > 0, sum the last n elements or x. If n < 0, sum the preceding elements.

Argument	Description
number	The last N number of rows to return the sum for. If N <= 0 use all previous rows.
metric	The metric for which you would like the Cumulative Sum.

Examples

Date	Revenue	CUMULATIVE(0, Revenue)	CUMULATIVE(2, Revenue)
May	$500	$500	$500
June	$200	$700	$700
July	$400	$1100	$600

Cumulative (Average)

CUMULATIVE AVERAGE(number, metric)

Returns the average of the last n elements of column x. If n > 0, sum the last n elements or x. If n < 0, sum the preceding elements.

Argument	Description
number	The last N number of rows to return the average for. If N <= 0 use all previous rows.
metric	The metric for which you would like the Cumulative Average.

Equal

EQUAL()

Equal. The output is either a 0 (false) or 1 (true).

Argument	Description
metric_X
metric_Y

Example

Metric 1 = Metric 2

Exponential regression: Correlation coefficient

EXPONENTIAL REGRESSION: CORRELATION COEFFICIENT(metric_X, metric_Y, include_zeros)

Table Exponential regression: Y = a exp(X) + b. Returns the correlation coefficient.

Argument	Description
metric_X	A metric that you would like to correlate with metric_Y
metric_Y	A metric that you would like to correlate with metric_X
include_zeros	Whether or not to include zero values in the calculations

Exponential regression: Predicted Y

EXPONENTIAL REGRESSION: PREDICTED Y(metric_X, metric_Y, include_zeros)

Row Exponential regression: Y = a exp(X) + b. Returns Y.

Argument	Description
metric_X	A metric that you would like to designate as the independent data.
metric_Y	A metric that you would like to designate as the dependent data.
include_zeros	Whether or not to include zero values in the calculations

Exponential regression: Intercept

EXPONENTIAL REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

Table Exponential regression: Y = a exp(X) + b. Returns b.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Exponential regression: Slope

EXPONENTIAL REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

Table Exponential regression: Y = a exp(X) + b. Returns a.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Floor

FLOOR(metric_X, metric_Y, include_zeros)

Row Returns the largest integer not greater than a given value. For example, if you want to avoid reporting currency decimals for revenue and a product has $569.34, use the formula FLOOR(Revenue) to round revenue down to the nearest dollar, or $569.

Argument	Description
metric	The metric that you want to round.

Greater Than

GREATER THAN()

The output is either a 0 (false) or 1 (true).

Argument	Description
metric_X
metric_Y

Example

Metric 1 > Metric 2

Greater Than or Equal

GREATER THAN OR EQUAL()

Greater than or equal. The output is either a 0 (false) or 1 (true).

Argument	Description
metric_X
metric_Y

Example

Metric 1 >= Metric 2

Hyperbolic Cosine

HYPERBOLIC COSINE(metric)

Row Returns the hyperbolic cosine of a number.

Argument	Description
metric	The angle in radians for which you want to find the hyperbolic cosine

Hyperbolic Sine

HYPERBOLIC SINE(metric)

Row Returns the hyperbolic sine of a number.

Argument	Description
metric	The angle in radians for which you want to find the hyperbolic sine

Hyperbolic Tangent

HYPERBOLIC TANGENT(metric)

Row Returns the hyperbolic tangent of a number.

Argument	Description
metric	The angle in radians for which you want to find the hyperbolic tangent

If

IF(logical_test, value_if_true, value_if_false)

Row If the value of the condition parameter is non-zero (true), the result is the value of the value_if_true parameter. Otherwise, it is the value of the value_if_false parameter.

Argument	Description
logical_test	Required. Any value or expression that can be evaluated to TRUE or FALSE
value_if_true	The value that you want to be returned if the logical_test argument evaluates to TRUE. (This argument defaults to 0 if not included.)
value_if_false	The value that you want to be returned if the logical_test argument evaluates to FALSE. (This argument defaults to 0 if not included.)

Less Than

LESS THAN()

The output is either a 0 (false) or 1 (true).

Argument	Description
metric_X
metric_Y

Example

Metric 1 < Metric 2

Less Than or Equal

LESS THAN OR EQUAL()

Less than or equal. The output is either a 0 (false) or 1 (true).

Argument	Description
metric_X
metric_Y

Example

Metric 1 <= Metric 2

Linear regression: Correlation coefficient

LINEAR REGRESSION: CORRELATION COEFFICIENT(metric_X, metric_Y, include_zeros)

Table Linear regression: Y = a X + b. Returns the correlation coefficient

Argument	Description
metric_X	A metric that you would like to correlate with metric_Y
metric_Y	A metric that you would like to correlate with metric_X
include_zeros	Whether or not to include zero values in the calculations

Linear regression: Intercept

LINEAR REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

Table Linear regression: Y = a X + b. Returns b.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Linear regression: Predicted Y

LINEAR REGRESSION: PREDICTED Y(metric_X, metric_Y, include_zeros)

Row Linear regression: Y = a X + b. Returns Y.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Linear regression: Slope

LINEAR REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

Table Linear regression: Y = a X + b. Returns a.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Log Base 10

LOG BASE 10(metric)

Row Returns the base-10 logarithm of a number.

Argument	Description
metric	The positive real number for which you want the base-10 logarithm

Log regression: Correlation coefficient

LOG REGRESSION: CORRELATION COEFFICIENT(metric_X, metric_Y, include_zeros)

Table Log regression: Y = a ln(X) + b. Returns the correlation coefficient.

Argument	Description
metric_X	A metric that you would like to correlate with metric_Y
metric_Y	A metric that you would like to correlate with metric_X
include_zeros	Whether or not to include zero values in the calculations

Log regression: Intercept

LOG REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

Table Log regression: Y = a ln(X) + b. Returns b.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Log regression: Predicted Y

LOG REGRESSION: PREDICTED Y(metric_X, metric_Y, include_zeros)

Row Log regression: Y = a ln(X) + b. Returns Y.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Log regression: Slope

LOG REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

Table Log regression: Y = a ln(X) + b. Returns a.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Natural Log

NATURAL LOG(metric)

Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). LN is the inverse of the EXP function.

Argument	Description
metric	The positive real number for which you want the natural logarithm

Not

NOT(logical)

Negation as a boolean. The output is either 0 (false) or 1 (true).

Argument	Description
logical	Required. A value or expression that can be evaluated to TRUE or FALSE

Not Equal

NOT EQUAL()

Not Equal. The output is either a 0 (false) or 1 (true).

Argument	Description
metric_X
metric_Y

Example

Metric 1 != Metric 2

Or

OR(logical_test)

Row Disjunction. Not equal to zero is considered to be true and equals zero is considered to be false. The output is either a 0 (false) or 1 (true).

Argument	Description
logical_test	Requires at least one parameter but can take any number of parameters. Any value or expression that can be evaluated to TRUE or FALSE

Pi

PI()

Returns Pi: 3.14159…

Power regression: Correlation coefficient

POWER REGRESSION: CORRELATION COEFFICIENT(metric_X, metric_Y, include_zeros)

Table Power regression: Y = b X ^ a. Returns the correlation coefficient.

Argument	Description
metric_X	A metric that you would like to correlate with metric_Y
metric_Y	A metric that you would like to correlate with metric_X
include_zeros	Whether or not to include zero values in the calculations

Power regression: Intercept

POWER REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

Table Power regression: Y = b X ^ a. Returns b.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Power regression: Predicted Y

POWER REGRESSION: PREDICTED Y(metric_X, metric_Y, include_zeros)

Row Power regression: Y = b X ^ a. Returns Y.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Power regression: Slope

POWER REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

Table Power regression: Y = b X ^ a. Returns a.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Quadratic regression: Correlation coefficient

QUADRATIC REGRESSION: CORRELATION COEFFICIENT(metric_X, metric_Y, include_zeros)

Table Quadratic regression: Y = (a + bX) ^ 2, Returns the correlation coefficient.

Argument	Description
metric_X	A metric that you would like to correlate with metric_Y
metric_Y	A metric that you would like to correlate with metric_X
include_zeros	Whether or not to include zero values in the calculations

Quadratic regression: Intercept

QUADRATIC REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

Table Quadratic regression: Y = (a + bX) ^ 2, Returns a.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Quadratic regression: Predicted Y

QUADRATIC REGRESSION: PREDICTED Y(metric_X, metric_Y, include_zeros)

Row Quadratic regression: Y = (a + bX) ^ 2, Returns Y.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Quadratic regression: Slope

QUADRATIC REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

Table Quadratic regression: Y = (a + bX) ^ 2, Returns b.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Reciprocal regression: Correlation coefficient

RECIPROCAL REGRESSION: CORRELATION COEFFICIENT(metric_X, metric_Y, include_zeros)

Table Reciprocal regression: Y = a + b X ^ -1. Returns the correlation coefficient.

Argument	Description
metric_X	A metric that you would like to correlate with metric_Y
metric_Y	A metric that you would like to correlate with metric_X
include_zeros	Whether or not to include zero values in the calculations

Reciprocal regression: Intercept

RECIPROCAL REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

Table Reciprocal regression: Y = a + b X ^ -1. Returns a.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Reciprocal regression: Predicted Y

RECIPROCAL REGRESSION: PREDICTED Y(metric_X, metric_Y, include_zeros)

Row Reciprocal regression: Y = a + b X ^ -1. Returns Y.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Reciprocal regression: Slope

RECIPROCAL REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

Table Reciprocal regression: Y = a + b X ^ -1. Returns b.

Argument	Description
metric_X	A metric that you would like to designate as the dependent data
metric_Y	A metric that you would like to designate as the independent data
include_zeros	Whether or not to include zero values in the calculations

Sine

SINE(metric)

Row Returns the sine of the given angle. If the angle is in degrees, multiply the angle by PI( )/180.

Argument	Description
metric	The angle in radians for which you want the sine

T-Score

T-SCORE(metric, include_zeros)

The deviation from the MEAN, divided by the standard deviation. Alias for Z-Score.

Argument	Description
metric	The metric for which you would like the T Score
include_zeros	Whether or not to include zero values in the calculations

T-Test

T-TEST(metric, degrees, tails)

Performs an m-tailed t-test with t-score of x and n degrees of freedom.

Argument	Description
metric	The metric on which you would like to perform a T Test
degrees	The degrees of freedom
tails	The length of the tail to be used to perform the T Test

Details

The signature is T-TEST(metric, degrees, tails). Underneath, it simply calls m CDF-T(-ABSOLUTE VALUE(tails), degrees). This function is similar to the Z-TEST function, which runs m CDF-Z(-ABSOLUTE VALUE(tails)).

• m is the number of tails.
• n is the degrees of freedom, and should be a constant number for the whole report, that is, not changing on a row by row basis.
• x is the T-test statistic, and would often be a formula (for example, Z-SCORE) based on a metric and is evaluated on every row.

The return value is the probability of seeing the test statistic x given the degrees of freedom and number of tails.

Examples:

1. Use the function to find outliers:

   T-TEST(Z-SCORE(bouncerate), ROW COUNT - 1, 2)
   

2. Combine the function with IF to ignore very high or low bounce rates, and count sessions on everything else:

   IF(T-TEST(Z-SCORE(bouncerate), ROW COUNT - 1, 2) < 0.01, 0, sessions )
   

Tangent

TANGENT(metric)

Returns the tangent of the given angle. If the angle is in degrees, multiply the angle by PI( )/180.

Argument	Description
metric	The angle in radians for which you want the tangent

Z-Score

Z-SCORE(metric, include_zeros)

Row The deviation from the mean divided by the standard deviation.

Argument	Description
metric	The metric for which you would like the Z Score
include_zeros	Whether or not to include zero values in the calculations

A Z-score of 0 (zero) implies the score is the same as the mean. A Z-score can be positive or negative, indicating whether it is above or below the mean and by how many standard deviations.

The equation for Z-score is:

Where x is the raw score, μ is the mean of the population, and σ is the standard deviation of the population.

Z-Test

Z-TEST(metric_tails)

Performs an n-tailed z-test with a z-score of x.

Argument	Description
metric	The metric on which you would like to perform a Z Test
tails	The length of the tail to be used to perform the Z Test