Essbase @MAXSRANGE

Returns the maximum value of the specified member across the specified
range of members, with options to skip missing or zero values (in contrast
with the @MAXRANGE function,
which cannot ignore these values).

Syntax

@MAXSRANGE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [ ,XrangeList])
Parameter Description

SKIPNONE

Includes all cells specified in expList in
the operation, regardless of their content

SKIPMISSING

Ignores all #MISSING values

SKIPZERO

Ignores all 0 values

SKIPBOTH

Ignores all 0 and #MISSING values

mbrName

Any valid single member name or member combination, or a function that
returns a single member or member combination

XrangeList

A valid member name, a comma-delimited list of member names, cross-dimension
members, or a member set function or range function (including @XRANGE) that
returns a list of members from the same dimension. If XrangeList is
not specified, Essbase uses the level 0 members from
the dimension tagged as time.

Notes

  • @MAXSRANGE (SKIPNONE, mbrName, XrangeList)
    is equivalent to @MAXRANGE mbrName, (XrangeList).

  • #MISSING values are considered to be greater
    than negative data values and less than positive data values. If the data
    being calculated includes only negative and #MISSING values,
    @MAXSRANGE returns #MISSING.

  • For all members, @MAXSRANGE returns the value calculated for
    the specified member and range list.

Example

For both examples, assume a database similar to Sample Basic. The Measures
dimension includes two members: COGS (cost of goods sold) and OtherInc_Exp
(miscellaneous income and expenses). The data can include 0 and #MISSING values.
For both members of the Measures dimension, the result is the same–the maximum
value for the OtherInc_Exp member across the specified range.

Example 1

Qtr1_Max = @MAXSRANGE (SKIPBOTH, OtherInc_Exp, @CHILDREN(Qtr1));

This example ignores #MISSING and 0 values and produces
the following results:

                   Jan       Feb       Mar  Qtr1_Max
              ========  ========  ========  ========
OtherInc_Exp      -500  #MISSING      -250      -250
COGS                 0      1500      2300      -250 

Example 2

Qtr1_Max = @MAXSRANGE (SKIPNONE, OtherInc_Exp, @CHILDREN(Qtr1));

Using the same data as Example 1, Example 2 demonstrates what happens
if you do not skip 0 and #MISSING values in the data. Example
2 produces the following report:

                   Jan       Feb       Mar  Qtr1_Max
              ========  ========  ========  ========   
OtherInc_Exp      -500  #MISSING      -250  #MISSING
COGS                 0      1500      2300  #MISSING 

See Also