Access these functions by checking Show Advanced in the Functions drop-down list.
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.
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.
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 |
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. |
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:
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.
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.
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. |
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. |
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. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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 )
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
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. |
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. |
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. |
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 |
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)
Returns items that match exactly for a numeric or string value.
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. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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. |
Returns items whose numeric count is greater than the value entered.
Returns items whose numeric count is greater than or equal to the value entered.
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. |
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. |
Returns the hyperbolic tangent of a number.
TANH(metric)
Argument | Description |
---|---|
metric | The angle in radians for which you want to find the hyperbolic tangent. |
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. |
[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.) |
Returns items whose numeric count is less than the value entered.
Returns items whose numeric count is less than or equal to the value entered.
Y = a X + b. Returns the correlation coefficient
Y = a X + b. Returns b.
Y = a X + b. Returns Y.
Y = a X + b. Returns a.
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. |
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. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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 independent data. |
metric_B | A metric that you would like to designate as the dependent data. |
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. |
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.
Returns all items that do not contain the exact match of the value entered.
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 |
Returns the constant PI, 3.14159265358979, accurate to 15 digits.
PI()
The PIfunction has no arguments.
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. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
Returns the correlation coefficient, r, between two metric columns (metric_X and metric_Y) for Y=(aX+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. |
Returns the intercept, b, between two metric columns (metric_X and metric_Y) for Y=(aX+b)***.
INTERCEPT.POWER(metric_X, metric_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. |
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=(aX+b)*** .
ESTIMATE.QUADRATIC(metric_A, metric_B)
Argument | Description |
---|---|
metric_A | A metric that you would like to designate as the independent data. |
metric_B | A metric that you would like to designate as the dependent data. |
Returns the slope, a, between two metric columns (metric_X and metric_Y) for Y=(aX+b)***.
SLOPE.QUADRATIC(metric_X, metric_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. |
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. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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 independent data. |
metric_Y | A metric that you would like to designate as the dependent data. |
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. |
Alias for Z-Score, namely the deviation from the mean divided by the standard deviation
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:
Use it to find outliers:
t_test( zscore(bouncerate), row-count-1, 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 )
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. |
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. |
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.