@VARIANCE
The @VARIANCE calculation function for Essbase calculates the statistical variance of the specified data set, based upon a sample.
The calculation is based upon a sample of a population. Variance is a measure of the dispersion of a set of data points around their mean (average) value.
Syntax
@VARIANCE (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
-
@VARIANCE is different from @VAR, which calculates the variance (difference) between two members.
-
@VARIANCE assumes that the data set (XrangeList) represents a sample of the population. If you want the data set to represent the entire population, use @VARIANCEP.
-
@VARIANCE is calculated with the "unbiased" or "n-1" method.
-
@VARIANCE uses the following formula:
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" = @VARIANCE(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 3687.58 4166.67
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" = @VARIANCE(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
See Also