@MEDIAN
The @MEDIAN calculation function for Essbase returns the median (the middle number) of the specified data set. Half the numbers in the data set are larger than the median, and half are smaller.
Syntax
@MEDIAN (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, XrangeList)
Parameters
- SKIPNONE
-
Includes all cells specified in the data set, regardless of their content, during calculation of the median.
- SKIPMISSING
-
Excludes all
#MISSING
values from the data set during calculation of the median. - SKIPZERO
-
Excludes all zero (0) values from the data set during calculation of the median.
- SKIPBOTH
-
Excludes all zero (0) values and
#MISSING
values from the data set during calculation of the median. - XrangeList
-
A list of numeric values across which the median 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
-
If the member you are calculating and the data set (XrangeList) are not in the same dimension, use @RANGE or @XRANGE to cross the member with the list of members (for example, to cross Sales with the children of 100).
-
@MEDIAN sorts the data set in ascending order before calculating the median.
-
When the data set contains an even number of values, @MEDIAN calculates the average of the two middle numbers.
-
@MEDIAN treats
#MISSING
values as 0 unless SKIPMISSING or SKIPBOTH is specified. -
When you use this function in a calculation script, use it within a FIX statement. Although FIX is not required, using it may improve calculation performance.
-
When you use this function across a large range in a sparse dimension, you may need to increase the size of the calculator cache.
Example
The following example is based on the Sample Basic database. Assume that the Measures dimension contains an additional member, Median. This example calculates the median sales values for all products and uses @RANGE to generate the data set:
FIX (Product)
Median = @MEDIAN(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
Median Product 625 575
Because SKIPBOTH is specified in the calculation script, the #MI
values for Diet Drinks are skipped. The remaining four products create an even-numbered data set. So, to calculate Median->Product->Actual, the two middle numbers in the set (587 and 663) are averaged to create the median (625). To calculate Median->Product->Budget, the two middle numbers in the set (530 and 620) are averaged to create the median (575).
The following example assumes a Year dimension is added to Sample Basic. It calculates median using cross-dimensional members in the data set.
FIX(Product)
Median = @MEDIAN(@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