Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Previous sectionNext section


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                                  *
Copy code to clipboard

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
Copy code to clipboard

See Also