Essbase @CORRELATION

Returns the correlation coefficient between two parallel data sets (expList1 and expList2).
The correlation coefficient determines the relationship between two data sets.

Syntax

@CORRELATION (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList1, expList2)
Parameter Description
SKIPNONE Includes all cells specified in expList1 and expList2, regardless of their content, during calculation
of the correlation coefficient.
SKIPMISSING Excludes all #MISSING values from expList1 and expList2 during calculation of the correlation coefficient.
SKIPZERO Excludes all zero (0) values from expList1 and expList2 during calculation of the correlation coefficient.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList1 and expList2 during
calculation of the correlation coefficient.
expList1 The first list of member specifications, variable names, functions,
or other numeric expressions.
expList2 The second list of member specifications, variable names, functions,
or other numeric expressions.

Notes

  • For complete information about using the @RANGE function,
    see @RANGE.
  • The expList1 and expList2 parameters must have the same number of
    data points. If expList1 and expList2 have different numbers of data points, @CORRELATION
    returns #MISSING.
  • The @CORRELATION function returns #MISSING if expList1 and expList2 (1)
    are empty, (2) contain only #MISSING values, or (3) have a standard
    deviation of 0 (all values are constant).
  • The @CORRELATION function treats #MISSING values
    as zero (0) values, unless SKIPMISSING or SKIPBOTH is specified. If a value
    in expList1 is #MISSING, and
    SKIPMISSING is specified, the value’s corresponding value in expList2 is
    treated as #MISSING. (That is, both values are deleted before
    calculation.) SKIPZERO and SKIPBOTH work similarly.
  • The @CORRELATION function returns values from -1 to 1.
  • If you use a member set function to generate a member list
    for this function (for example, @SIBLINGS), to ensure correct results, consider
    the order in which Essbase sorts the generated member
    list. For more information, see the Oracle Essbase Technical Reference topic for the member set function
    you are using.
  • The equation for the correlation coefficient is:

Example

The following example is based on the Sample Basic database. Assume
that the Measures dimension contains an additional member, Sales Correl. The
calculation script calculates the correlation coefficient for a set of members
(Sales for the children of Qtr1 and Qtr2). Because the calculation script
fixes on Jun, the results are placed in Sales Correl->Jun.

This example uses the @RANGE function
to generate expList1 and expList2:

FIX(June)
"Sales Correl"=@CORRELATION(SKIPNONE,
@RANGE(Sales,@CHILDREN(Qtr1)),@RANGE(Sales,@CHILDREN(Qtr2)));
ENDFIX

This example produces the following report:

             Colas     Actual    New York
               Sales       Sales Correl
               =====       ============
Jan             678             #MI
Feb             645             #MI
Mar             675             #MI
Apr             712             #MI
May             756             #MI
Jun             890        0.200368468

See Also

  • @RANGE