Search

FIRSTNONBLANK

Shows the first non-blank text-, list-, or date-formatted value for a line item for a given list item. FIRSTNONBLANK is an aggregation function you can use to reference a line item in a formula. The result of the aggregation function must have the same data type as the source.

There is also a summary method, First non-blank, which you can use with number-, text-, list-, and date-formatted line items.

Syntax

x[FIRSTNONBLANK: y]

where:

  • x: Number-, text-, list-, date-, or time period-formatted line item to search
  • y: List item or time period to use as a condition

Format

Input Format Output Format

x: Number-, text-, list-, date-, or time period-formatted line item

y: Line item or property with data type specified to be list or time period values from one of the dimensions of the result line item.

Matches the data format used by argument x

Arguments

The function uses the following arguments:

  • x: Number, 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

Examples

For example, use FIRSTNONBLANK to show the first non-blank transaction for products sold to each customer in your Transactions Module. The Transactions Module has two dimensions: the Transactions list and line items. The line item:

  • Product Name is text-formatted.
  • Customers is list-formatted so that it can be used for mapping into the FIRSTNONBLANK Product and FIRSTNONBLANK Date results.
  • Date is date-formatted.
  • Value is number-formatted to two decimal places and USD currency.
  • Product Text is text-formatted. The Customers line item is list-formatted so that it can be used for mapping into the FIRSTNONBLANK Product and FIRSTNONBLANK Date results.

A module view with four line items named Product Name, Customers, Date, and Value on columns. On rows, there is a list with 20 items named Transaction 01 through Transaction 20.

A view of the Customer Summary module. Two line items called FIRSTNONBLANK Product and FIRSTNONBLANK date show the first non-blank item for their respective line items in the Transaction Summary module for each customer in the Customers list.

The Customer Summary module contains two dimensions, the Customers list and line items. Any line items that contain the FIRSTNONBLANK function must have the same format as the line item being searched. This means the FIRSTNONBLANK Product line item must be text-formatted and the FIRSTNONBLANK Date line item must be date-formatted.

The formula for FIRSTNONBLANK Product line item is:

Transactions Module.Product Name[FIRSTNONBLANK: Transactions Module.Customers]

The formula for FIRSTNONBLANK Date line item is:

Transactions Module.Date[FIRSTNONBLANK: Transactions Module.Customers]

Summary Method

There's also a summary method called First non-blank that can be used with normal totals. In Blueprint:

  1. scroll right to the Summary column,
  2. select the line item you want to use the First non-blank summary method for,
  3. click the ellipsis in the right of the cell,
  4. select First non-blank from the Summary dropdown,
  5. click OK.

The Blueprint view for the Transactions module, showing the Summary column for each line item.

In this example, the First non-blank summary method has been applied to the Product Name, Customers, and Date line items. The result displays in the Total row.

Similar functions