Standard Deviation Functions

Standard deviation function return the statistical standard deviation of all values in the set based on a sample of the population (STDEV), or based on a biased population (STDEVP).

Standard deviation is useful for measuring variance within a data set and, in application, confidence in statistical results. For example, in finance, standard deviation can measure the potential deviation from expected return rate, measuring the volatility of the investment. Depending on the number of values in the sample set, you should use either of the following metrics.

  • STDEV (numeric), also known as the corrected standard deviation 

     

    • where numeric is a fact or metric

    • Computes the sum of squared differences from the mean for n values divided by a sample size factor. If the input values are a sample of the total (biased) population, the divisor is (n - 1) sample size, which is used in the STDEV function.

    • This value is the variance. See Variance Functions. The square root of the above value represents the standard deviation.

  • STDEVP (numeric), also known as the uncorrected standard deviation

 

-   where *numeric* is a fact or metric
-   Computes square root of \[the sum of squared differences from the mean for *n* values divided by *n* \]
-   Use STDEVP for calculating standard deviations if the input set contains the entire (unbiased) population of values. The deviation for a single value is zero.

Syntax

SELECT STDEV(…)
SELECT STDEV(metric)
SELECT STDEVP(fact)

Examples

SELECT STDEV(Internal Rate of Return)
SELECT STDEVP(Gross Weight)

Using Standard Deviation

You can use standard deviation to create ranges as other metrics. Suppose you have a standard deviation metric (StDev Transaction Amt):

Example 1: +1 Std.

SELECT Avg Transaction Amt + StDev Transaction Amt

Example 2: +2 Std.

SELECT Avg Transaction Amt + (StDev Transaction Amt * 2)

Example 3: -1 Std.

SELECT Avg Transaction Amt + (StDev Transaction Amt * -1)

Example 4: -2 Std.

SELECT Avg Transaction Amt + (StDev Transaction Amt * -2)

Sample Chart

Using the examples above, you can create a combo chart that shows all individual transactions (X axis) and where they fall within bands of +2/-2 standard deviations (Y axis).