1. Calculation functions
  2. All Functions
  3. Aggregation Functions
  4. LASTNONBLANK

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

ArgumentData typeDescription
Values to search Date, time period, list, textThe value to search for the last occurrence of. 
Mapping itemDate, 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.

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 occurs 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.

Related Anaplan functions

Examples

In the Source Customers module below, Product text is text-formatted and Customers is list-formatted.  

Source CustomersProduct TextCustomersDateValue
1
Customer A01/04/20151000
2BananasCustomer A
900
3GrapefruitCustomer B15/05/20151200
4OrangesCustomer B19/05/20151000
5ApplesCustomer A20/05/20151000
6GrapefruitCustomer B
1200
7OrangesCustomer B26/05/2015800
8BananasCustomer B28/05/2015900
9
Customer A29/05/2015900
10OrangesCustomer B30/05/20151000

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 SummaryCustomer ACustomer B

LASTNONBLANK Product

Source Customers.Product Text[LASTNONBLANK: Source Customers.Customers]

ApplesOranges

LASTNONBLANK Date

Source Customers.Date[LASTNONBLANK: Source Customers.Customers]

29/05/201530/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 CustomersProduct TextCustomersDate
1
Customer A01/04/2015
2BananasCustomer A
3GrapefruitCustomer B15/05/2015
4OrangesCustomer B19/05/2015
5ApplesCustomer A20/05/2015
6GrapefruitCustomer B
7OrangesCustomer B26/05/2015
8BananasCustomer B28/05/2015
9
Customer A29/05/2015
10OrangesCustomer B30/05/2015
TotalOrangesCustomer B30/05/2015