Essbase variance

Difference between two values (for example, planned and actual value).

Essbase @VARIANCE

Calculates the statistical variance of the specified data set (expList). 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, expList)
Parameter Description
SKIPNONE Includes all cells specified in expList,
regardless of their content, during calculation of the variance.
SKIPMISSING Excludes all #MISSING values from expList during
calculation of the variance.
SKIPZERO Excludes all zero (0) values from expList during
calculation of the variance.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the variance.
expList Comma-delimited list of member specifications, variable names, functions,
or numeric expressions. expList provides
a list of numeric values across which the variance is calculated.

Notes

  • @VARIANCE is different from @VAR, which calculates the variance
    (difference) between two members.
  • @VARIANCE assumes that expList represents
    a sample of the population. If you want expList 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 expList, 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

See Also

  • @VARIANCEP