Show Menu
TOPICS×

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.
0 (zero) means False, and any other value is True.
AND(logical_test1,[logical_test2],...)
Argument
Description
logical_test1
Required. Any value or expression that can be evaluated to TRUE or FALSE.
logical_test2
Optional. Additional conditions that you want to evaluate as TRUE or FALSE

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)
Argument
dimension
The dimension for which you want the approximate distinct item count.

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)
Argument
metric
The cosine of the angle you want from -1 to 1.

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) 
Argument
metric
The cosine of the angle you want from -1 to 1.

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)
Argument
metric
The cosine of the angle you want from -1 to 1.

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

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)
Argument
Description
metric
The metric that you want to round.

Cosine (Row)

Returns the cosine of the given angle. If the angle is in degrees, multiply the angle by PI( )/180.
COS(metric)
Argument
Description
metric
The angle in radians for which you want the cosine.

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)
Argument
Description
metric
The metric for which you want the cube root.

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.
This does not work as you might expect with rate metrics like revenue/visitor: it averages the rates instead of summing revenue over the last N and summing visitors over the last N and then dividing them. Instead, use
cumul(revenue)/cumul(visitor)

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

Exponential Regression: Intercept (Table)

Returns the intercept, b, between two metric columns ( metric_X and metric_Y) for
INTERCEPT.EXP(metric_X, metric_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.

Exponential Regression: Slope (Table)

Returns the slope, a, between two metric columns ( metric_X and metric_Y) for .
SLOPE.EXP(metric_X, metric_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.

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)
Argument
Description
metric
The metric you want to round.

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)
Argument
Description
metric
The angle in radians for which you want to find the hyperbolic cosine.

Hyperbolic Sine (Row)

Returns the hyperbolic sine of a number.
SINH(metric)
Argument
Description
metric
The angle in radians for which you want to find the hyperbolic sine.

Hyperbolic Tangent (Row)

Returns the hyperbolic tangent of a number.
TANH(metric)
Argument
Description
metric
The angle in radians for which you want to find the hyperbolic tanget.

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])
Argument
Description
logical_test
Required. Any value or expression that can be evaluated to TRUE or FALSE.
The value that you want to be returned if the logical_test argument evaluates to TRUE. (This argument defaults to 0 if not included.)
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

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.

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)
Argument
Description
metric
The positive real number for which you want the base-10 logarithm.

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

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

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

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)
Argument
Description
metric_A
A metric that you would like to designate as the dependent data.
metric_B
A metric that you would like to designate as the independent data.

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)
Argument
Description
metric
The positive real number for which you want the natural logarithm.

NOT

Returns 1 if the number is 0 or returns 0 if another number.
NOT(logical)
Argument
Description
logical
Required. A value or expression that can be evaluated to TRUE or FALSE.
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.
0 (zero) means False, and any other value is True.
OR(logical_test1,[logical_test2],...)
Argument
Description
logical_test1
Required. Any value or expression that can be evaluated to TRUE or FALSE.
logical_test2
Optional. Additional conditions that you want to evaluate as TRUE or FALSE

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

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

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

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

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

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

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)
Argument
Description
metric_A
A metric that you would like to designate as the dependent data.
metric_B
A metric that you would like to designate as the dependent data.

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

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

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

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

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

Sine (Row)

Returns the sine of the given angle. If the angle is in degrees, multiply the angle by PI( )/180.
SIN(metric)
Argument
Description
metric
The angle in radians for which you want the sine.

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:
  1. Use it to find outliers:
    t_test( zscore(bouncerate), row-count-1, 2)
  2. Combine it with if to ignore very high or low bounce rates, and count visits on everything else:
    if ( t_test( z-score(bouncerate), row-count, 2) < 0.01, 0, visits )

Tangent

Returns the tangent of the given angle. If the angle is in degrees, multiply the angle by PI( )/180.
TAN (metric)
Argument
Description
metric
The angle in radians for which you want the tangent.

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.
μ (mu) andσ (sigma) are automatically calculated from the metric.
Z-score(metric)
Argument Description
metric
Returns the value of its first non-zero argument.

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.
Assumes that the values are normally distributed.