@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