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]{class="badge neutral"} or [Row]{class="badge neutral"}

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

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

Approximate Count Distinct approximate_count_distinct

APPROXIMATE COUNT DISTINCT(dimension)

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

Example

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

Arc Cosine arc-cosine

ARC COSINE(metric)

[Row]{class="badge neutral"} 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().

Arc Sine arc-sine

ARC SINE(metric)

[Row]{class="badge neutral"} 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().

Arc Tangent arc-tangent

ARC TANGENT(metric)

[Row]{class="badge neutral"} 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().

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

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

CDF-Z(metric, number)

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

Examples

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

Ceiling ceiling

CEILING(metric)

[Row]{class="badge neutral"} 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.

Confidence confidence

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

Calculate the any-time-valid confidence 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.

Confidence (Lower) 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.

Confidence (Upper) 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.

Cosine cosine

COSINE(metric)

[Row]{class="badge neutral"} Returns the cosine of the given angle. If the angle is in degrees, multiply the angle by PI()/180.

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

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

Examples

Cumulative (Average) 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.

NOTE

This function does not work with rate metrics like revenue per person. The function averages the rates instead of summing revenue over the last N and summing persons over the last N and then dividing them.
Instead, use CUMULATIVE(revenue) CUMULATIVE(person).

Equal equal

EQUAL()

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

Example

Metric 1 = Metric 2

Exponential regression: Correlation coefficient exponential-regression-correlation-coefficient

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

[Table]{class="badge neutral"} Exponential regression: Y = a exp(X) + b. Returns the correlation coefficient.

Exponential Regression: Predicted Y exponential-regression-predicted-y

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

[Row]{class="badge neutral"} Exponential regression: Y = a exp(X) + b. Returns Y.

Exponential Regression: Intercept exponential-regression-intercept

EXPONENTIAL REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Exponential regression: Y = a exp(X) + b. Returns b.

Exponential Regression: Slope exponential-regression-slope

EXPONENTIAL REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Exponential regression: Y = a exp(X) + b. Returns a.

Floor floor

FLOOR(metric_X, metric_Y, include_zeros)

[Row]{class="badge neutral"} 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.

Greater Than greather-than

GREATER THAN()

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

Example

Metric 1 > Metric 2

Greater Than or Equal greater-than-or-equal

GREATER THAN OR EQUAL()

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

Example

Metric 1 >= Metric 2

Hyperbolic Cosine hyperbolic-cosine

HYPERBOLIC COSINE(metric)

[Row]{class="badge neutral"} Returns the hyperbolic cosine of a number.

Hyperbolic Sine hyperbolic-sine

HYPERBOLIC SINE(metric)

[Row]{class="badge neutral"} Returns the hyperbolic sine of a number.

Hyperbolic Tangent hyperbolic-tangent

HYPERBOLIC TANGENT(metric)

[Row]{class="badge neutral"} Returns the hyperbolic tangent of a number.

If if

IF(logical_test, value_if_true, value_if_false)

[Row]{class="badge neutral"} 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.

Less Than less-than

LESS THAN()

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

Example

Metric 1 < Metric 2

Less Than or Equal less-than-or-equal

LESS THAN OR EQUAL()

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

Example

Metric 1 <= Metric 2

Lift (#lift)

Linear Regression: Correlation coefficient linear-regression-correlation-coefficient

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

[Table]{class="badge neutral"} Linear regression: Y = a X + b. Returns the correlation coefficient.

Linear Regression: Intercept linear-regression-intercept

LINEAR REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Linear regression: Y = a X + b. Returns b.

Linear Regression: Predicted Y linear-regression-predicted-y

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

[Row]{class="badge neutral"} Linear regression: Y = a X + b. Returns Y.

Linear Regression: Slope linear-regression-slope

LINEAR REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Linear regression: Y = a X + b. Returns a.

Log Base 10 log-base-ten

LOG BASE 10(metric)

[Row]{class="badge neutral"} Returns the base-10 logarithm of a number.

Log Regression: Correlation coefficient log-regression-correlation-coefficient

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

[Table]{class="badge neutral"} Log regression: Y = a ln(X) + b. Returns the correlation coefficient.

Log Regression: Intercept log-regression-intercept

LOG REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Log regression: Y = a ln(X) + b. Returns b.

Log Regression: Predicted Y log-regression-predicted-y

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

[Row]{class="badge neutral"} Log regression: Y = a ln(X) + b. Returns Y.

Log Regression: Slope log-regression-slope

LOG REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Log regression: Y = a ln(X) + b. Returns a.

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

Not not

NOT(logical)

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

Not Equal not-equal

NOT EQUAL()

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

Example

Metric 1 != Metric 2

Or or

OR(logical_test)

[Row]{class="badge neutral"} 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).

Pi pi

PI()

Returns Pi: 3.14159…

Power Regression: Correlation coefficient power-regression-correlation-coefficient

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

[Table]{class="badge neutral"} Power regression: Y = b X ^ a. Returns the correlation coefficient.

Power Regression: Intercept power-regression-intercept

POWER REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Power regression: Y = b X ^ a. Returns b.

Power Regression: Predicted Y power-regression-predicted-y

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

[Row]{class="badge neutral"} Power regression: Y = b X ^ a. Returns Y.

Power Regression: Slope power-regression-slope

POWER REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Power regression: Y = b X ^ a. Returns a.

Quadratic Regression: Correlation coefficient quadratic-regression-correlation-coefficient

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

[Table]{class="badge neutral"} Quadratic regression: Y = (a + bX) ^ 2, Returns the correlation coefficient.

Quadratic Regression: Intercept quadratic-regression-intercept

QUADRATIC REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Quadratic regression: Y = (a + bX) ^ 2, Returns a.

Quadratic Regression: Predicted Y quadratic-regression-predicted-y

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

[Row]{class="badge neutral"} Quadratic regression: Y = (a + bX) ^ 2, Returns Y.

Quadratic Regression: Slope quadratic-regression-slope

QUADRATIC REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Quadratic regression: Y = (a + bX) ^ 2, Returns b.

Reciprocal Regression: Correlation coefficient reciprocal-regression-correlation-coefficient

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

[Table]{class="badge neutral"} Reciprocal regression: Y = a + b X ^ -1. Returns the correlation coefficient.

Reciprocal Regression: Intercept reciprocal-regression-intercept

RECIPROCAL REGRESSION: INTERCEPT(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Reciprocal regression: Y = a + b X ^ -1. Returns a.

Reciprocal Regression: Predicted Y reciprocal-regression-predicted-y

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

[Row]{class="badge neutral"} Reciprocal regression: Y = a + b X ^ -1. Returns Y.

Reciprocal Regression: Slope reciprocal-regression-slope

RECIPROCAL REGRESSION: SLOPE(metric_X, metric_Y, include_zeros)

[Table]{class="badge neutral"} Reciprocal regression: Y = a + b X ^ -1. Returns b.

Sine sine

SINE(metric)

[Row]{class="badge neutral"} Returns the sine of the given angle. If the angle is in degrees, multiply the angle by PI()/180.

T-Score t-score

T-SCORE(metric, include_zeros)

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

T-Test t-test

T-TEST(metric, degrees, tails)

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

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

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

Examples

Tangent tangent

TANGENT(metric)

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

Z-Score z-score

Z-SCORE(metric, include_zeros)

[Row]{class="badge neutral"} The deviation from the mean divided by the standard deviation.

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

Z-TEST(metric_tails)

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