Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see Why Migrate to InterSystems IRIS?


Returns the value of the given measure (or other numeric expression) evaluated for the last non-empty member of a set. This function is an InterSystems extension to MDX.

Returned Type

This function returns a number.

Syntax and Details

%LAST(set_expression, optional_numeric_expression)


  • 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, DeepSee 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, DeepSee 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.


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