The aggregation function FIRSTNONBLANK returns the first value of a line item found for a given list item or time period. 

This function is useful if you want to show the first non-blank record in a list of products sold to each customer.

Line item to search [FIRSTNONBLANK: Mapping, FIRSTNONBLANK: Mapping 2, etc.]

ArgumentData typeDescription
Values to searchDate, time period, list, textThe value to search for the first occurrence of.
MappingDate, time period, list

The mapping that determines which values to find the first non-blank value for.

Each instance of this argument must be a dimension present in the Values to sum argument.

This argument can be repeated to provide multiple mappings.

The FIRSTNONBLANK function returns a value that matches the type of Value to search argument. The line item that contains the FIRSTNONBLANK function must be dimensioned by all dimensions used for the Mapping argument.

First 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 First non-blank.

The FIRSTNONBLANK 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 FIRSTNONBLANK.

You can reference the Users list with the FIRSTNONBLANK function. However, you can't reference specific users within the Users list as this is production data, which can change and make your formula invalid.

The dimensions of the Mapping argument must also be dimensions of the Values to search argument.

You cannot use the FIRSTNONBLANK aggregation function in Polaris.

In the Classic engine, you can.

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 Customer Summary results module below, holds the results of FIRSTNONBLANK.  The FIRSTNONBLANK Product result line item is text-formatted. The FIRSTNONBLANK Date result line item is date-formatted.

Customer SummaryCustomer ACustomer B

FIRSTNONBLANK Product

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

BananasGrapefruit

FIRSTNONBLANK Date

Source Customers.Date[FIRSTNONBLANK: Source Customers.Customers]

01/04/201515/05/2015

The First non-blank summary method behaves in a similar way to the FIRSTNONBLANK function. In the table below, the First 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
TotalBananas Customer A01/04/2015