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

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

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.

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

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

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