Returns the maximum value of the specified member across the specified range of members, with options to skip missing or zero values.
@MAXSRANGE (SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [ ,XrangeList])
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, Analytic Services uses the level 0 members from the dimension tagged as time. |
This function returns the maximum value of the specified
member across the specified range of members. @MAXSRANGE enables
skipping of #MISSING
and 0 values, in contrast with the
@MAXRANGE
function, which cannot ignore these values.
#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 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.
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
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
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |