Reference - advanced functions

Access these functions by checking Show Advanced in the Functions drop-down list.

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.

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 this to affect any MEAN, MIN, QUARTILE, etc. calculations that you have on the revenue column. In this case, you would check the include-zeros parameter.

On the other hand, if you have two metrics that you are interested in, it may not be fair to say that one has a higher average or minimum because some of its rows were zeros, so you would not check the parameter to include the zeros.

AND

Returns the value of its argument. Use NOT to make sure that a value is not equal to one particular value.

AND(logical_test1,[logical_test2],...)

Approximate Count Distinct (dimension)

Returns the approximated distinct count of dimension items for the selected dimension. The function uses the HyperLogLog (HLL) method of approximating distinct counts.  It is configured to guarantee the value is within 5% of the actual value 95% of the time.

Approximate Count Distinct (dimension)

Example Use Case

Approximate Count Distinct (customer ID eVar) is a common use case for this function.

Definition for a new ‘Approximate Customers’ calculated metric:

This is how the “Approximate Customers” metric could be used in reporting:

Uniques Exceeded

Like Count() and RowCount(), Approximate Count Distinct() is subject to “uniques exceeded” limits. If the “uniques exceeded” limit is reached within a particular month for a dimension, the value is counted as 1 dimension item.

Comparing Count Functions

Approximate Count Distinct() is an improvement over Count() and RowCount() functions because the metric created can be used in any dimensional report to render an approximated count of items for a separate dimension. For example, a count of customer IDs used in a Mobile Device Type report.

This function will be marginally less accurate than Count() and RowCount() because it uses the HLL method, whereas Count() and RowCount() are exact counts.

Arc Cosine (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( ).

ACOS(metric)

Arc Sine (Row)

Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is 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( ).

ASIN(metric)

Arc Tangent (Row)

Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is 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( ).

ATAN(metric)

Exponential Regression: Predicted Y (Row)

Calculates the predicted y-values (metric_Y), given the known x-values (metric_X) using the “least squares” method for calculating the line of best fit based on .

ESTIMATE.EXP(metric_X, metric_Y)

Cdf-T

Returns the percentage of values in a student’s t-distribution with n degrees of freedom that have a z-score less than x.

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

Returns the percentage of values in a normal distribution that have a z-score less than x.

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

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

CEILING(metric)

Confidence

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.

fx Confidence (normalizing-container, success-metric, control, significance-threshold)

Cosine (Row)

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

COS(metric)

Cube Root

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.

CBRT(metric)

Cumulative

Returns the sum of x for the last N rows (as ordered by the dimension, using hash values for string based fields).

If N <= 0 it uses all previous rows. Since it’s ordered by the dimension it’s only useful on dimensions that have a natural order like date or path length.

| Date | Rev  | cumul(0,Rev) | cumul(2,Rev) |
|------+------+--------------+--------------|
| May  | $500 | $500         | $500         |
| June | $200 | $700         | $700         |
| July | $400 | $1100        | $600         |

Cumulative Average

Returns the average of the last N rows.

If N <= 0 it uses all previous rows. Since it’s ordered by the dimension it’s only useful on dimensions that have a natural order like date or path length.

cumul(revenue)/cumul(person)

Equal

Returns items that match exactly for a numeric or string value.

Exponential Regression_ Correlation Coefficient (Table)

Returns the correlation coefficient, r, between two metric columns ( metric_A and metric_B) for the regression equation .

CORREL.EXP(metric_X, metric_Y)

Exponential Regression: Intercept (Table)

Returns the intercept, b, between two metric columns ( metric_X and metric_Y) for

INTERCEPT.EXP(metric_X, metric_Y)

Exponential Regression: Slope (Table)

Returns the slope, a, between two metric columns ( metric_X and metric_Y) for .

SLOPE.EXP(metric_X, metric_Y)

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

FLOOR(metric)

Greater Than

Returns items whose numeric count is greater than the value entered.

Greater Than or Equal

Returns items whose numeric count is greater than or equal to the value entered.

Hyperbolic Cosine (Row)

Returns the hyperbolic cosine of a number.

COSH(metric)

Hyperbolic Sine (Row)

Returns the hyperbolic sine of a number.

SINH(metric)

Hyperbolic Tangent (Row)

Returns the hyperbolic tangent of a number.

TANH(metric)

IF (Row)

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.

IF(logical_test, [value_if_true], [value_if_false])

Less Than

Returns items whose numeric count is less than the value entered.

Less Than or Equal

Returns items whose numeric count is less than or equal to the value entered.

Lift

Returns the Lift a particular variant had in conversions over a control variant. It is the difference in performance between a given variant and the baseline, divided by the performance of the baseline, expressed as a percentage.

fx Lift (normalizing-container, success-metric, control)

Linear regression_ Correlation Coefficient

Y = a X + b. Returns the correlation coefficient

Linear regression_ Intercept

Y = a X + b. Returns b.

Linear regression_ Predicted Y

Y = a X + b. Returns Y.

Linear regression_ Slope

Y = a X + b. Returns a.

Log Base 10 (Row)

Returns the base-10 logarithm of a number.

LOG10(metric)

Log regression: Correlation coefficient (Table)

Returns the correlation coefficient, r, between two metric columns (metric_X and metric_Y) for the regression equation Y = a ln(X) + b. It is calculated using the CORREL equation.

CORREL.LOG(metric_X,metric_Y)

Log regression: Intercept (Table)

Returns the intercept b as the least squares regression between two metric columns (metric_X and metric_Y) for the regression equation Y = a ln(X) + b. It is calculated using the INTERCEPT equation.

INTERCEPT.LOG(metric_X, metric_Y)

Log Regression: Predicted Y (Row)

Calculates the predicted y values (metric_Y), given the known x values (metric_X) using the “least squares” method for calculating the line of best fit based on Y = a ln(X) + b. It is calculated using the ESTIMATE equation.

In regression analysis, this function calculates the predicted y values (metric_Y), given the known x values (metric_X) using the logarithm for calculating the line of best fit for the regression equation Y = a ln(X) + b. The a values correspond to each x value, and b is a constant value.

ESTIMATE.LOG(metric_X, metric_Y)

Log regression: Slope (Table)

Returns the slope, a, between two metric columns (metric_X and metric_Y) for the regression equation Y = a ln(X) + b. It is calculated using the SLOPE equation.

SLOPE.LOG(metric_A, metric_B)

Natural Log

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.

LN(metric)

NOT

Returns 1 if the number is 0 or returns 0 if another number.

NOT(logical)

Using NOT requires knowing if the expressions (<, >, =, <> , etc.) return 0 or 1 values.

Not equal

Returns all items that do not contain the exact match of the value entered.

Or (Row)

Returns TRUE if any argument is TRUE, or returns FALSE if all arguments are FALSE.

OR(logical_test1,[logical_test2],...)

Pi

Returns the constant PI, 3.14159265358979, accurate to 15 digits.

PI()

The PIfunction has no arguments.

Power regression: Correlation coefficient (Table)

Returns the correlation coefficient, r, between two metric columns (metric_X and metric_Y) for Y = b*X.

CORREL.POWER(metric_X, metric_Y)

Power regression: Intercept (Table)

Returns the intercept, b, between two metric columns (metric_X and metric_Y) for Y = b*X.

 INTERCEPT.POWER(metric_X, metric_Y)

Power regression: Predicted Y (Row)

Calculates the predicted y values ( metric_Y), given the known x values ( metric_X) using the “least squares” method for calculating the line of best fit for Y = b*X.

 ESTIMATE.POWER(metric_X, metric_Y)

Power regression: Slope (Table)

Returns the slope, a, between two metric columns (metric_X and metric_Y) for Y = b*X.

SLOPE.POWER(metric_X, metric_Y)

Quadratic regression: Correlation coefficient (Table)

Returns the correlation coefficient, r, between two metric columns (metric_X and metric_Y) for Y=(a X+b)***.

CORREL.QUADRATIC(metric_X, metric_Y)

Quadratic regression: Intercept (Table)

Returns the intercept, b, between two metric columns (metric_X and metric_Y) for Y=(a X+b)***.

INTERCEPT.POWER(metric_X, metric_Y)

Quadratic regression: Predicted Y (Row)

Calculates the predicted y values (metric_Y), given the known x values (metric_X) using the least squares method for calculating the line of best fit using Y=(a X+b)*** .

ESTIMATE.QUADRATIC(metric_A, metric_B)

Quadratic regression: Slope (Table)

Returns the slope, a, between two metric columns (metric_X and metric_Y) for Y=(a X+b)***.

SLOPE.QUADRATIC(metric_X, metric_Y)

Reciprocal regression: Correlation coefficient (Table)

Returns the correlation coefficient, r, between two metric columns (metric_X) and metric_Y) for Y = a/X+b.

CORREL.RECIPROCAL(metric_X, metric_Y)

Reciprocal regression: Intercept (Table)

Returns the intercept, b, between two metric columns (metric_X and metric_Y) for Y = a/X+b.

INTERCEPT.RECIPROCAL(metric_A, metric_B)

Reciprocal regression: Predicted Y (Row)

Calculates the predicted y values (metric_Y), given the known x values (metric_X) using the least squares method for calculating the line of best fit using Y = a/X+b.

ESTIMATE.RECIPROCAL(metric_X, metric_Y)

Reciprocal regression: Slope (Table)

Returns the slope, a, between two metric columns (metric_X and metric_Y) for Y = a/X+b.

SLOPE.RECIPROCAL(metric_X, metric_Y)

Sine (Row)

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

SIN(metric)

T-Score

Alias for Z-Score, namely the deviation from the mean divided by the standard deviation

T-Test

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

The signature is t_test( x, n, m ). Underneath, it simply calls m*cdf_t(-abs(x),n). (This is similar to the z-test function which runs m*cdf_z(-abs(x)).

Here, m is the number of tails, and n is the degrees of freedom. These should be numbers (constant for the whole report, i.e. not changing on a row by row basis).

X is the t-test statistic, and would often be a formula (e.g. zscore) based on a metric and will be 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:

Tangent

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

TAN (metric)

Z-Score (Row)

Returns the Z-score, or normal score, based upon a normal distribution. The Z-score is the number of standard deviations an observation is from the mean. A Z-score of 0 (zero) means 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-score(metric)

Z-Test

Performs an n-tailed Z-test with Z-score of A.

Returns the probability that the current row could be seen by chance in the column.