LastNonNull

The LastNotNull function returns the last non-Null value in a column or grouping.

LastNonNull() is either an aggregate function, or a window function. As a window function, it repeats the last non-Null value for every row in the column.

This is similar to the Last() function, but excludes Null values.

Data sort order is important when using this function: Sigma applies sorting and filtering before the function. Your CDW determines the default sort order.

Syntax

LastNonNull(\[Column\])

The function has the following argument:

Column

The column input

Accepts all data types

Examples

LastNonNull(\[Delivery Date\]) // Window function

Here the calculation references a column in a lower grouping level. This example shows the oldest non-null date from the Delivery Date column for each grouping under the Order Number column.

Delivery Date is in descending order sort. If Delivery Date is in ascending sort, the function returns the most recent non-Null date for each grouping.

LastNonNull(\[Delivery Date\]) // Aggregate function

Here the calculation references a column in the same grouping level, so the first non-Null value  returns for every row.

Note how the resulting calculations depend on the group’s sort order. The sort on the Product Family affects the order of the Delivery Date column.