FILL_NA
FILL_NA('Node' [, "Dimension" [, Value]])
The FILL_NA function fills missing values (N/A) on existing level combinations after the specified dimension is pivoted into columns.
Use this function when reshaping or pivoting creates missing cells and you want to explicitly fill those gaps with a specific value.
Parameters
- NodeNode referenceRequired
- Node that may contain missing values (N/A).
- DimensionDimension nameOptional
- Name of the dimension to pivot into columns before filling. Default:
"Time"at the node’s finest level. - ValueNumberOptional
- Value written into former missing cells on existing level combinations. Default:
0.
Watch out
- The node must already contain levels of the specified dimension.
- The function fills only where the dimension is pivoted into columns.
- It does not fill the complete time horizon and does not create missing level values that are not present in the input node (e.g., absent products). To fill missing values, use FILLMISSING or FILLMISSING_LAST.
- Linked levels of the chosen dimension also appear as columns in the pivoted result.
Examples
Filling missing time values with 0 (default)
This example fills missing values after pivoting the time dimension into columns. The default fill value is 0.
The time horizon of the project 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 "2026", "2028" and "Car", "Van".
Input node: 'Input node'
| Year | Product | Value |
|---|---|---|
| 2026 | Car | 100 |
| 2028 | Van | 90 |
Pivoted Input node by dimension "Time"
| Product | 2026 | 2028 |
|---|---|---|
| Car | 100 | N/A |
| Van | N/A | 90 |
Formula: FILL_NA('Input node')
Equivalent to: FILL_NA('Input node', "Time", 0)
| Product | 2026 | 2028 |
|---|---|---|
| Car | 100 | 0 |
| Van | 0 | 90 |
This function fills the missing level values of the finest time dimension with 0. Notice that the formula does not fill the complete time horizon (e.g. expands the years "2025" and "2027").
Filling along a custom dimension with a custom value
This example fills missing values along the ProductDimension using 16 as the fill value instead of the default 0.
Formula: FILL_NA('Input node', "ProductDimension", 16)
| Year | Car | Van |
|---|---|---|
| 2026 | 100 | 16 |
| 2028 | 16 | 90 |
This function fills the missing level values of the dimension "ProductDimension" with 16. Notice that the formula does not add any values available in the dimension that are not present in the input (e.g. "Truck").
See also
- FILLMISSING
- When you want to add missing level values for a dimension, not just fill N/A cells from pivoting.
- FILLMISSING_LAST
- When you want to fill missing values using the last available value instead of a constant.