@MODE

The @MODE calculation function for Essbase returns the mode (the most frequently occurring value) in the specified data set.

Syntax

@MODE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, XrangeList)

Parameters

SKIPNONE

Includes all cells specified in the data set, regardless of their content, during calculation of the mode.

SKIPMISSING

Excludes all #MISSING values from the data set during calculation of the mode.

SKIPZERO

Excludes all zero (0) values from the data set during calculation of the mode.

SKIPBOTH

Excludes all zero (0) values and #MISSING values from the data set during calculation of the mode.

XrangeList

A list of numeric values across which the mode 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

• When two or more values in the data set 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 the data set 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 the data set contains no duplicate values, this function returns the smallest value in the list as the mode. For example, if the data set contains [2,4,7,10,14], @MODE returns 2 as the mode.

• If #MISSING is the mode of the data set, this function returns #MISSING unless SKIPMISSING or SKIPBOTH is specified. If you specify SKIPMISSING or SKIPBOTH and all values in the data set are #MISSING, this function returns #MISSING. If you specify SKIPZERO or SKIPBOTH and all values in the data set are 0, this function returns #MISSING.

• 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 calculates the mode of the units sold for the Central region and uses @RANGE to generate the data set:

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 

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

FIX(Product)
"Mode" = @MODE(SKIPMISSING,@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

See Also

• @RANGE

• @XRANGE