Statistical Calculations

Ad hoc analysis incorporates statistical calculations to use when building calculated metrics, allowing you to quickly apply descriptive calculations to your calculated metrics, segments, and report data.

Statistical calculations include mean, standard deviation, correlation, and additional calculations used in calculated metrics.

Statistical Functions

Exponential Functions

Logarithmic Regression Functions

Power Functions

Quadratic Functions

Reciprocal Functions

Statistical Functions

CDF-T

Note: Where metric is identified as an argument in a function, other expressions of metrics are also allowed. For example, MAXV(metrics) also allows for MAXV(PageViews + Visits).

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

CORREL

Returns the Pearson correlation coefficient, r, between two metric columns (metric_x and metric_y). Use the correlation coefficient to determine the linear relationship between two metrics with ranges from -1.0 to 1.0, inclusive.

The equation for CORREL is:



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

ESTIMATE

Calculates the line of best fit to estimate predicted metric values using the y=ax+b equation.

The equation evaluates ESTIMATE (dependent variable (metric_Y), independent variable (metric_X)).

ESTIMATE(metric_Y, metric_X)
Argument Description
metric_Y A metric that you would like to designate as the dependent data.
metric_X A metric that you would like to designate as the independent data.

INTERCEPT

Calculates the point at which a line will intersect the y axis by using existing x values (metric_X) and y values (metric_Y). The value of b is calculated using the Slope function.

The intercept point is based on a best-fit regression line plotted through the known a values and known b values. Use the INTERCEPT function when you want to determine the value of the dependent variable (metric_X) when the independent variable (metric_Y) is 0 (zero) using the formula:

y=ax+b

where b is the slope and x and y are the means, MEAN(metric_A) and MEAN(metric_B).

INTERCEPT(metric)
where b is the slope and x and y are the means, MEAN(metric_A) and MEAN(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.

MAXH

Returns the largest value across a set of metrics for a specific dimension element. MAXH evaluates horizontally across columns (metrics).

MAXH(metric_X,metric_Y,...)
Argument Description
metric_X A metric that you would like to have evaluated.
metric_Y A metric that you would like to have evaluated.

MAXV

Returns the largest value in a set of dimension elements for a metric column. MAXV evaluates vertically within a single column (metric) across dimension elements.

MAXV(metric)
Argument Description
metric A metric that you would like to have evaluated.

MEAN

Returns the arithmetic mean, or average, for a metric in a column.

MEAN(metric)
Argument Description
metric The metric for which you want the average.

MEDIAN

Returns the median for a metric in a column. The median is the number in the middle of a set of numbers—that is, half the numbers have values that are greater than or equal to the median, and half are less than or equal to the median.

MEDIAN(metric)
Argument Description
metric The metric for which you want the median.

MINH

Returns the smallest value across a set of metrics for a specific dimension element. MINH evaluates horizontally across columns (metrics) for a specific dimension element.

MINH(metric_X, metric_Y, ...)
Argument Description
metric_X A metric that you would like to have evaluated.
metric_Y A metric that you would like to have evaluated.

MINV

Returns the smallest value in a set of dimension elements for a metric column. MINV evaluates vertically within a single column (metric) across dimension elements.

MINV(metric)
Argument Description
metric A metric that you would like to have evaluated.

PERCENTILE

Returns the k-th percentile of values for a metric. You can use this function to establish a threshold of acceptance. For example, you can decide to examine dimension elements who score above the 90th percentile.

PERCENTILE(metric,k)
Argument Description
metric The metric column that defines relative standing.

k

The percentile value in the range 0 to 100, inclusive.

QUARTILE

Returns the quartile of values for a metric. For example, quartiles can be used to find the top 25% of products driving the most revenue. MINV, MEDIAN, and MAXV return the same value as QUARTILE when quart is equal to 0 (zero), 2, and 4, respectively.

QUARTILE(metric,quart)
Argument Description
metric The metric for which you want the quartile value.

quart

Indicates which *value to return.

*If quart = 0, QUARTILE returns the minimum value. If quart = 1, QUARTILE returns the first quartile (25th percentile). If quart = 2, QUARTILE returns the first quartile (50th percentile). If quart = 3, QUARTILE returns the first quartile (75th percentile). If quart = 4, QUARTILE returns the maximum value.

SLOPE

Returns the slope of the linear regression line through two metrics columns (metric_X and metric_Y). The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.

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

STDEV

Returns the standard deviation, or square root of the variance, based on a sample population of data.

The equation for STDEV is:



where x is the sample mean (metric) and n is the sample size.

STDEV(metric)
Argument Description
metric

The metric for which you want for standard deviation.

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 x and n degrees of freedom.

Returns the probability that the current row could be seen by chance in the column. Note: Assumes that the values are distributed according to the student t-distribution with n degrees of freedom.

t_test( x, n, m )

VARIANCE

Returns the variance based on a sample population of data.

The equation for VARIANCE is:

where x is the sample mean, MEAN(metric), and n is the sample size.

VARIANCE(metric)
Argument Description
metric The metric for which you want the variance.

Z-score

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.

Note: μ (mu) and σ (sigma) are calculated from the metric automatically.
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 x.

Returns the probability that the current row could be seen by chance in the column. Note: Z-test assumes that the values are normally distributed.

Exponential Functions

CORREL.EXP

Returns the correlation coefficient, r, between two metric columns (metric_A and metric_B) for the regression equation y = b*exp( a*x ).

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.

ESTIMATE.EXP

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 = b*exp(a*X)

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.

INTERCEPT.EXP

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

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.

SLOPE.EXP

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

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.

Logarithmic Regression Functions

CORREL.LOG

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.

ESTIMATE.EXP

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 = b*exp(a*X)

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.

ESTIMATE.LOG

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.

INTERCEPT.LOG

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.

SLOPE.LOG

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.

Power Functions

CORREL.POWER

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

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.

ESTIMATE.POWER

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*Xa.

 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.

INTERCEPT.POWER

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

 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.

SLOPE.POWER

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

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 Functions

CORREL.QUADRATIC

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

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.

ESTIMATE.QUADRATIC

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

ESTIMATE.POWER(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.

INTERCEPT.QUADRATIC

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

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.

SLOPE.QUADRATIC

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

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 Functions

CORREL.RECIPROCAL

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.

ESTIMATE.RECIPROCAL

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.

INTERCEPT.RECIPROCAL

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

INTERCEPT.POWER(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.

SLOPE.RECIPROCAL

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.