@RANGELASTVAL

Returns the last value, in a range of the specified mbrList, that satisfies the criterion specified in the first function parameter.

Syntax

@RANGELASTVAL(SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrList)
Parameter Description
SKIPNONE Every cell value is considered as data.
SKIPMISSING #MISSING values are not considered as data.
SKIPZERO Zero (0) values are not considered as data.
SKIPBOTH Zero (0) and #MISSING values are not considered as data.
mbrList A valid member name, a comma-delimited list of member names, cross dimension members, or a member set function that returns a list of members from the same dimension. If you use the range operator or a function, the order of mbrList is dictated by the database outline order.

Notes

The function returns #MISSING when mbrList does not contain any value matching the criterion specified in the first argument.

Example

Example 1

In the following example, @RANGELASTVAL sets Jan’s budget sales of Diet Cola to the last actual sales of Qtr1.

FIX("100-10", "New York", "Sales", "Jan")
"Budget" = @RANGELASTVAL(SKIPBOTH, @CHILDREN(Qtr1)->"Actual");
ENDFIX

As indicated by the SKIPBOTH parameter, @RANGELASTVAL skips zero and #MISSING. The mbrList parameter is provided by the @CHILDREN expression.

The following examples use the Sample.Basic database.

Example 2

@RANGELASTVAL(SKIPMISSING, @CHILDREN("Qtr1"));

or

@RANGELASTVAL(SKIPMISSING, "Jan":"Mar");

or

@RANGELASTVAL(SKIPMISSING, ("Jan","Feb","Mar"))

The previous statements return the last non-#MISSING value found when sequentially looking up the values of members Jan, Feb, and Mar.

Example 3

@RANGELASTVAL(SKIPZERO, @CHILDREN("Jan"));

Because member Jan does not have any children, it returns #MISSING.