@CORRELATION

This function returns the correlation coefficient between two parallel data sets (XrangeList1 and XrangeList2). The correlation coefficient determines the relationship between two data sets.

Syntax

@CORRELATION (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, XrangeList1, XrangeList2)

Parameters

SKIPNONE

Includes all cells specified in the two data sets, regardless of their content, during calculation of the correlation coefficient.

SKIPMISSING

Excludes all #MISSING values from the two data sets during calculation of the correlation coefficient.

SKIPZERO

Excludes all zero (0) values from the two data sets during calculation of the correlation coefficient.

SKIPBOTH

Excludes all zero (0) values and #MISSING values from the two data sets during calculation of the correlation coefficient.

XrangeList1

The first of two parallel data sets.

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 in the topic Range and Financial Functions.

XrangeList2

The second of two parallel data sets.

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).

Notes

For complete information about using the @RANGE function, see @RANGE. For more information about XrangeList, see Range List Parameters in the topic Range and Financial Functions.
The XrangeList1 and XrangeList2 parameters must have the same number of data points. If the two data sets have different numbers of data points, this function returns #MISSING.
This function returns #MISSING if XrangeList1 and XrangeList2 (1) are empty, (2) contain only #MISSING values, or (3) have a standard deviation of 0 (all values are constant).
This function treats #MISSING values as zero (0) values, unless SKIPMISSING or SKIPBOTH is specified. If a value in XrangeList1 is #MISSING, and SKIPMISSING is specified, the value's corresponding value in XrangeList1 is treated as #MISSING. (That is, both values are deleted before calculation.) SKIPZERO and SKIPBOTH work similarly.
This 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 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 XrangeList1 and XrangeList2:

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

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

FIX(Product)
"Sales Correl" = @CORRELATION(SKIPNONE,@XRANGE("2011"->"Sep", "2012"->"Mar"),@XRANGE("2012"->"Sep", "2013"->"Mar"));
ENDFIX

The correlation above is calculated across the following two multidimensional ranges specified by XrangeList1 and XrangeList2:


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


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