TOPICS×
Reference: advanced functions
Access these functions by checking
Show Advanced
in the
Functions
dropdown 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 IncludeZeros 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 includezeros 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 yvalues (metric_Y), given the known xvalues (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.

CdfT
Returns the percentage of values in a student's tdistribution with n degrees of freedom that have a zscore 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 )
CdfZ
Returns the percentage of values in a normal distribution that have a zscore 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 base10 logarithm of a number.
LOG10(metric)
Argument
 Description


metric
 The positive real number for which you want the base10 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.

TScore
Alias for ZScore, namely the deviation from the mean divided by the standard deviation
TTest
Performs an mtailed ttest with tscore 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 ztest 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 ttest 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), rowcount1, 2)
 Combine it with if to ignore very high or low bounce rates, and count visits on everything else:
if ( t_test( zscore(bouncerate), rowcount, 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.

ZScore (Row)
Returns the Zscore, or normal score, based upon a normal distribution. The Zscore is the number of standard deviations an observation is from the mean. A Zscore of 0 (zero) means the score is the same as the mean. A Zscore can be positive or negative, indicating whether it is above or below the mean and by how many standard deviations.
The equation for Zscore 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.
Zscore(metric)
Argument  Description 

metric  Returns the value of its first nonzero argument.

ZTest
Performs an ntailed Ztest with Zscore of A.
Returns the probability that the current row could be seen by chance in the column.
Assumes that the values are normally distributed.