The aggregation function LASTNONBLANK returns the last value of a line item found for a given list item or time period.
This function is useful if you want to show the last non-blank record in the list of products sold to each customer.
Syntax
Line item to search[LASTNONBLANK: Mapping, LASTNONBLANK: Mapping 2, etc.]
Arguments
| Argument | Data type | Description |
| Values to search | Date, time period, list, text | The value to search for the last occurrence of. |
| Mapping item | Date, time period, list | The mapping that determines which values to find the last non-blank value for. Each instance of this argument must be a dimension present in the Values to search argument. This argument can be repeated to provide multiple mappings. |
The LASTNONBLANK function returns a value that matches the type of Value to search argument. The line item that contains the LASTNONBLANK function must be dimensioned by all dimensions used for the Mapping argument.
Calculation engine functionality differences
You cannot use the LASTNONBLANK aggregation function in Polaris.
In the Classic engine, you can.
Additional information
Summary method
Last non-blank can also be used as a summary method. You can use the summary method on date, time period, list, and text-formatted line items.
In Blueprint, scroll right to the Summary column and select the method as Last non-blank.
The LASTNONBLANK function and the summary method checks for values in the order of list items as they occur in General lists. If you hide values in a module or if the list items have parent items that change the order they display in, the order of the list items will change and this might impact the outcome of LASTNONBLANK.
Use FIRSTNONBLANK with the Users list
You can reference the Users list with the FIRSTNONBLANK function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.
Constraints
The dimensions of the Mapping argument must also be dimensions of the Values to search argument.
Examples
In the Source Customers module below, Product text is text-formatted and Customers is list-formatted.
| Source Customers | Product Text | Customers | Date | Value |
| 1 | Customer A | 01/04/2015 | 1000 | |
| 2 | Bananas | Customer A | 900 | |
| 3 | Grapefruit | Customer B | 15/05/2015 | 1200 |
| 4 | Oranges | Customer B | 19/05/2015 | 1000 |
| 5 | Apples | Customer A | 20/05/2015 | 1000 |
| 6 | Grapefruit | Customer B | 1200 | |
| 7 | Oranges | Customer B | 26/05/2015 | 800 |
| 8 | Bananas | Customer B | 28/05/2015 | 900 |
| 9 | Customer A | 29/05/2015 | 900 | |
| 10 | Oranges | Customer B | 30/05/2015 | 1000 |
The results module below, holds the results of LASTNONBLANK. The LASTNONBLANK Product result line item is text-formatted. The LASTNONBLANK Date result line item is date-formatted.
| Customer Summary | Customer A | Customer B |
LASTNONBLANK Product
| Apples | Oranges |
LASTNONBLANK Date
| 29/05/2015 | 30/05/2015 |
Summary method example
The Last non-blank summary method behaves in a similar way to the LASTNONBLANK function. In the table below, the Last non-blank summary method is applied to Product Text, Customers, and Date. The result displays in the Total row.
| Source Customers | Product Text | Customers | Date |
| 1 | Customer A | 01/04/2015 | |
| 2 | Bananas | Customer A | |
| 3 | Grapefruit | Customer B | 15/05/2015 |
| 4 | Oranges | Customer B | 19/05/2015 |
| 5 | Apples | Customer A | 20/05/2015 |
| 6 | Grapefruit | Customer B | |
| 7 | Oranges | Customer B | 26/05/2015 |
| 8 | Bananas | Customer B | 28/05/2015 |
| 9 | Customer A | 29/05/2015 | |
| 10 | Oranges | Customer B | 30/05/2015 |
| Total | Oranges | Customer B | 30/05/2015 |
