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