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 text-, list-, and date-formatted line items.
- x: Text-, list-, date-, or time period-formatted line item to search
- y: List item or time period to use as a condition
|Input Format||Output Format|
x: 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|
The function uses the following arguments:
- x: Text, list item, date, time period
- y: List item, time period
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.
The function has the following constraints:
- The result must have the same data type as the source.
- No Excel equivalent
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.
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]
There's also a summary method called First non-blank that can be used with normal totals. In Blueprint:
- scroll right to the Summary column,
- select the line item you want to use the First non-blank summary method for,
- click the ellipsis in the right of the cell,
- select First non-blank from the Summary dropdown,
- click OK.
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.