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.
Logarithmic Regression Functions
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( -∞ ) = 0 cdf_z( ∞ ) = 1 cdf_z( 0 ) = 0.5 cdf_z( 2 ) ? 0.97725 cdf_z( -3 ) ? 0.0013499
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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 90^{th} 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. |
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 (25^{th} percentile). If quart = 2, QUARTILE returns the first quartile (50^{th} percentile). If quart = 3, QUARTILE returns the first quartile (75^{th} percentile). If quart = 4, QUARTILE returns the maximum value.
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. |
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. |
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 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 )
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. |
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)
Argument | Description |
---|---|
metric |
Returns the value of its first non-zero argument. |
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.
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
Returns the correlation coefficient, r, between two metric columns (metric_X and metric_Y) for Y = b*X^{a}.
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. |
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^{a}.
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. |
Returns the intercept, b, between two metric columns (metric_X and metric_Y) for Y = b*X^{a}.
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. |
Returns the slope, a, between two metric columns (metric_X and metric_Y) for Y = b*X^{a}.
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |