Essbase @MODE

Returns the mode (the most frequently occurring value) in the specified
data set (expList).

Syntax

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

Notes

  • When two or more values in expList occur
    at the same frequency, Essbase sorts the list of values
    in ascending order and chooses the lowest value that occurs with the most
    frequency as the mode. For example, if expList contains
    [2,1,2,2,2,3,3,3,3], Essbase sorts the list as [1,2,2,2,2,3,3,3,3]
    and chooses the value [2] as the mode.
  • If expList contains no
    duplicate values, the @MODE function returns the smallest value in the list
    as the mode. For example, if expList contains
    [2,4,7,10,14], @MODE returns 2 as the mode.
  • If #MISSING is the mode of expList,
    @MODE returns #MISSING unless SKIPMISSING or SKIPBOTH is specified.
    If you specify SKIPMISSING or SKIPBOTH and all values in expList are #MISSING,
    @MODE returns #MISSING. If you specify SKIPZERO or SKIPBOTH and
    all values in expList are 0, @MODE returns #MISSING.
  • When you use @MODE in a calculation script, use it within
    a FIX statement. Although FIX is not required, using it may improve calculation
    performance.
  • When you use @MODE 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 calculates the mode of the units sold for the
Central region and uses the @RANGE function
to generate expList:

FIX (Central)
"Mode" = @MODE(SKIPMISSING,
   @RANGE(Sales,@CHILDREN(Central)));
ENDFIX

This example produces the following report:

                       Colas   Actual    Jan
                             Units Sold
                             ==========
Units Sold   Illinois            3
             Ohio                2
             Wisconsin           3
             Missouri          #MI
             Iowa                0
             Colorado            6
               Central          14                 

Mode         Central             3

See Also

  • @RANGE