@VARIANCEP

The calculation is based upon the entire population. Variance is a measure of the dispersion of a set of data points around their mean (average) value.

Syntax

@VARIANCEP (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, XrangeList)

Parameters

SKIPNONE

Includes all cells specified in the data set, regardless of their content, during calculation of the variance.

SKIPMISSING

Excludes all #MISSING values from the data set during calculation of the variance.

SKIPZERO

Excludes all zero (0) values from the data set during calculation of the variance.

SKIPBOTH

Excludes all zero (0) values and #MISSING values from the data set during calculation of the variance.

XrangeList

A list of numeric values across which the variance is calculated. Referred to generically throughout this topic as "the data set."

Can be a valid member name, a comma-delimited list of member names, cross dimensional members, or a return value from a member set function or range function (including @XRANGE).

For more information about XrangeList, see Range List Parameters.

Notes

@VARIANCEP is different from @VARPER, which calculates the percent variance (difference) between two members.
@VARIANCEP assumes that the data set (XrangeList) represents the entire population. If you want the data set to represent a sample of the population, use @VARIANCE.
@VARIANCEP is calculated using the "biased" or "n" method.
@VARIANCEP uses the following formula:

${n \sum x^2 - (\sum x)^2}\over n^2$

Example

The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Sales Var. This example uses the @RANGE function to generate the data set, and calculates the variance of the sales values for a product family.

FIX (Product)
"Sales Var" = @VARIANCEP(SKIPBOTH,@RANGE(Sales,@CHILDREN(Product)));
ENDFIX

This example produces the following report:

                                     Jan      New York
                                   Actual        Budget
                                   ======        ======
        Sales       Colas            678           640
                   Root Beer        551           530
                   Cream Soda       663           510
                   Fruit Soda       587           620
                   Diet Drinks      #MI           #MI
                    Product        2479          2300
        
        Sales Var   Product         2765.69       3125

The following example assumes a Year dimension is added to Sample Basic. It calculates variance using cross-dimensional members in the data set.

FIX(Product)
"Sales Var" = @VARIANCEP(SKIPBOTH,@XRANGE("2011"->"Sep", "2012"->"Mar"));
ENDFIX

The above calculation is performed across the following multidimensional range specified by XrangeList:


2011->Sep
2011->Oct
2011->Nov
2011->Dec
2012->Jan
2012->Feb
2012->Mar