# LastNonNull

Returns the last non-null value in a column or grouping.

**LastNonNull**() can be used as either an aggregate function or a window function. When used 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.

Syntax

LastNonNull(value)

## Arguments

Term |
Description |

value |
A column to reference or a formula to calculate. The last non-null value is derived from input. All column types are accepted. |

## Notes

Considering data sort order is important when using this function:

- All sorting and/or filtering is applied prior to calculating this function.
- If no sort order is explicitly set, Sigma does not force one. In this case, sort order is determined by your Cloud Data Warehouse.

## Examples

### Example #1

The example below shows the oldest non-null date from the **[Delivery Date]** column for each grouping under the **[Order Number] **column.

LastNonNull([Delivery Date])

Note how **[Delivery Date]** is sorted descending. If instead **[Delivery Date]** was sorted ascending, the function would return the most recent non-null date for each grouping.

### Example #2

The example below is much like Example #1. However, instead of using **LastNonNull**() as an aggregate function, we use it as a window function. This means the calculation references a column in the same group, instead of referencing a column in a lower group. As a result the first non-null value is returned for every row.

Also, note how the resulting calculations are dependent on the group’s sort order. In this example, the group of columns in which **LastNonNull**() is calculated is sorted by the **[Product Family]** column. This sort order impacts the order of the **[Delivery Date]** column, which is then passed to **LastNonNull**() in the **[Calc] **column.

## See Also

Last – Returns the last value in a column or grouping

FirstNonNull – Returns the first non-null value in a column or grouping