Essbase @MEDIAN

Returns the median (the middle number) of the specified data set (expList). Half the numbers in the data set are larger
than the median, and half are smaller.

Syntax

@MEDIAN (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, expList)
Parameter Description
SKIPNONE Includes all cells specified in expList,
regardless of their content, during calculation of the median.
SKIPMISSING Excludes all #MISSING values from expList during
calculation of the median.
SKIPZERO Excludes all zero (0) values from expList during
calculation of the median.
SKIPBOTH Excludes all zero (0) values and #MISSING values from expList during calculation of the median.
expList Comma-delimited list of member specifications, variable names, functions,
or numeric expressions. expList provides
a list of numeric values across which the median is calculated.

Notes

  • If the member you are calculating and expList are
    not in the same dimension, use the @RANGE function to cross the member with
    the list of members (for example, to cross Sales with the children of 100).
  • @MEDIAN sorts expList in
    ascending order before calculating the median.
  • When expList contains an
    even number of values, the @MEDIAN function calculates the average of the
    two middle numbers.
  • @MEDIAN treats #MISSING values as 0 unless SKIPMISSING
    or SKIPBOTH is specified.
  • When you use @MEDIAN in a calculation script, use it within
    a FIX statement. Although FIX is not required, using it may improve calculation
    performance.
  • When you use @MEDIAN across a large range in a sparse dimension,
    you may need to increase the size of the calculator cache. For more information
    on the calculator cache, see the Oracle Essbase Database Administrator’s Guide.

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 the @RANGE function to generate expList:

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

See Also

  • @RANGE