%LAST (MDX)

Returned Type

This function returns a number.

Syntax and Details

%LAST(set_expression, optional_numeric_expression)

Where:

• set_expression is an expression that evaluates to a set, typically a set of members or tuples.

• optional_numeric_expression is a numeric-valued expression that the function evaluates for each set element.

  Typically, this expression has the form [MEASURES].[measure_name]

  If you do not specify a numeric expression, the system uses the measure used by the current result cell. For example, this might be the measure used on the 0 axis or the measure specified in the WHERE clause, if any. If the query itself does not specify a measure, the system instead uses %COUNT, which counts records in the fact table.

The %LAST function returns the last non-missing value evaluated for each member of the given set.

Example

For reference, the following query shows patients with osteoporosis, grouped by birth decade:

SELECT MEASURES.[%Count] ON 0, birthd.decade.MEMBERS ON 1 FROM patients WHERE diagd.osteoporosis
 
                             Patient Count
 1 1910s                                  2
 2 1920s                                  5
 3 1930s                                 10
 4 1940s                                  5
 5 1950s                                  *
 6 1960s                                  *
 7 1970s                                  *
 8 1980s                                  *
 9 1990s                                  *
10 2000s                                  *
11 2010s                                  *

The following query uses %LAST to get the last non-empty set of patients from the preceding set:

SELECT MEASURES.[%Count] ON 0, %LAST(birthd.decade.MEMBERS) ON 1 FROM patients WHERE diagd.osteoporosis
 
                             Patient Count
LAST                                      5

See Also

• %FIRST