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.
Syntax
Line item to search [FIRSTNONBLANK: Mapping, FIRSTNONBLANK: Mapping 2, etc.]
Arguments
| Argument | Data type | Description |
| Values to search | Date, time period, list, text | The value to search for the first occurrence of. |
| Mapping | Date, 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.
Calculation engine functionality differences
You cannot use the FIRSTNONBLANK aggregation function in Polaris.
In the Classic engine, you can.
Additional information
Summary method
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.
Use FIRSTNONBLANK with the Users list
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.
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 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 Summary | Customer A | Customer B |
FIRSTNONBLANK Product
| Bananas | Grapefruit |
FIRSTNONBLANK Date
| 01/04/2015 | 15/05/2015 |
Summary method example
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 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 | Bananas | Customer A | 01/04/2015 |
