FILLMISSING

Category: Filtering & data shaping

Overview

The FILLMISSING function fills missing level values of a specified dimension into the input node’s data. The input node must already contain the levels of that dimension. FILLMISSING does not add new levels; it only fills missing level values of levels already present.

Use this function when you want to fill structurally missing level values in an existing dimension with a constant value.

Syntax

FILLMISSING('Node' [, "Dimension" [, Value [, "Behavior"]]])

Example usage: FILLMISSING('Revenue')

Parameters

Behavior options:

• "All": Fills all missing values across the full project horizon.
• "StartAtFirst": Fills missing entries after the first present value per partition, through the end of the horizon.
• "FinishAtLast": Fills missing entries from the start of the horizon until the last present value per partition.
• "Interval": Fills missing entries only between the first and last present values per partition.

Output Shape

Watch Out

• The input node must already contain levels of the specified dimension. FILLMISSING does not add new levels to the node.
• If the node has linked levels, you cannot fill values of the linked-to dimension. Fill the source dimension instead to get all valid values.
• This function enforces level links from dimension management. Do not use this function if you want the node to have different level links than the dimensions.
• The fill behavior operates per partition: for example, with "StartAtFirst", the start point is determined separately for each product.

Examples

Fill missing values across the time horizon

This example fills missing year and product values with a constant. The project time horizon is 2025-2028.

The dimension "ProductDimension" consists of the level "Product" and the level values "Car", "Van", and "Truck".

The input node features the level values "2025", "2027" and "Car", "Van".

Input node: Input node

Formula: FILLMISSING('Input node')

With no arguments, the input node is filled with the missing time level values (2026, 2028) using the default value 0.

Formula: FILLMISSING('Input node', "ProductDimension", 1)

Here, the input node is filled with the missing level values of "ProductDimension" (e.g. "Truck"). The fill value is 1.

Fill missing values with different behaviors

This example uses the same input with different behavior settings to show how the fill range changes.

Input node: Input node

Formula: FILLMISSING('Input node', "Time", "1", "All")

"All" fills empty values over the entire project horizon.

Formula: FILLMISSING('Input node', "Time", "1", "StartAtFirst")

"StartAtFirst" fills after the first value present for each product through the end of the horizon.

Formula: FILLMISSING('Input node', "Time", "1", "FinishAtLast")

"FinishAtLast" fills from the start of the horizon until the last value present for each product.

Formula: FILLMISSING('Input node', "Time", "1", "Interval")

"Interval" fills only between the first and last present values for each product.

Fill missing values with linked levels

Keep in mind that this function might change existing level values if the levels are linked. The result of this function always follows the links in the dimension table.

Input node: Input

Formula: FILLMISSING('Input', "TimeDimension", 15)

Notice that the CalendarMonth values in the input (06, 06, 11, 05, 06, 02) were corrected to match the dimension management links (01, 02, 03, …, 12). FILLMISSING enforces the linked level values.

Related Functions