Shows the last non-blank text, list or date cell and uses the mapping to determine where to post the result. LASTNONBLANK is available as an aggregation function in a selection clause, to be consistent with its availability as a summary method for date, list, time period, and text formats.
The result must have the same data type as the source.
Syntax
x[LASTNONBLANK: y]
where:
- x: Source: Text, list item, date, time period
- y: Mapping showing where the result is posted
Format
| Input Format | Output Format |
|---|---|
|
x: Text, list item, date, time period y: List item, time period |
Matches the data format used by argument x |
Arguments
The function uses the following arguments:
- x: Text, list item, date, time period
- y: List item, time period
Constraints
The function has the following constraints:
- The result must have the same data type as the source.
Excel equivalent
- No Excel equivalent
Example
Suppose you want to show the last non blank record in the list of products sold to each customer. The Source Customers source module has dimensions consisting of a transactional record list and line items.
Product Text is text-formatted. The Customers line item is list-formatted so that it can be used for mapping into the LASTNONBLANK Product and LASTNONBLANK Date results.
Source Customers.Product Text[LASTNONBLANK:Source Customer.Customers]
The Customer Summary results module has a dimensionality of Customers by line items. The LASTNONBLANK Product result line item must be text-formatted. The LASTNONBLANK Date result line item must be date-formatted.
The formula for LASTNONBLANK Product is:
Source Customers.Product Text[LASTNONBLANK: Source Customers.Customers]
The formula for LASTNONBLANK Date is:
Source Customers.Date[LASTNONBLANK: Source Customers.Customers]
Summary Example
Last non-blank can also be used as a summary for normal totals. In Blueprint, scroll right to the Summary column and select the method as Last non-blank.
For example, the Summary method selected for the Product Text line item is First non-blank. The Summary method selected for the Date line item is Last non-blank.
