FILLMISSING

Basic Overview

AVAILABLE FROM 5.2.0

Description	Fills up all the missing level values of a specified dimension to the data of the input node. The input node must already contain the levels of the dimension.
Signature	FILLMISSING('Node' [, "Dimension" [, "Value"]]) AVAILABLE FROM 5.8.0 FILLMISSING('Node' [, "Dimension" [, "Value" [, "Behavior"]]])
Parameters	Node: A node (with potentially missing level values) Dimension: Name of the dimension which level values will be used to fill up the input node. This function does not add levels to the input, it just fills up missing level values of the levels already present. The default fill up is done on the finest time level. Value: The value of the new rows. The default value is 0. Behavior: [optional] The filling behavior that the function will follow. Available behaviors: All: Default behavior. Fills all the empty values with the specified value over the horizon. StartAtFirst: Fills only the empty vaues with the specified value starting after the first entry of the specified dimension. FinishAtLast: Fills only the empty entries starting from the start of the project horizon and finishing at last entry of the specified dimension. Interval: Fills only the empty entries between first value present and last value present.
Limitations	Node must already contain levels of the specified dimension If the node contains linked levels, it is not possible to fill values of the dimension that is linked too. Fill the values that is linked from to get all the valid values instead. Important: This function always makes the links from the levels of the specified dimension be the same as in the dimension management. Do not use this function if you want the node to have different level links as the dimensions.

Example

The time horizon of the project is 2021-2024.

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

The input node features the level values '2021', 2023' and 'Car', 'Van'.

Input node =

Year	Product	Value
2021	Car	100
2023	Van	90

FILLMISSING('Input node') =

Year	Product	Value
2021	Car	100
2021	Van	0
2022	Car	0
2022	Van	0
2023	Car	0
2023	Van	90
2024	Car	0
2024	Van	0

With this formula, the input node is filled with the missing level values (2022, 2024) of the default dimension 'Time' and the default value '0' is added.

The input node now features all years of the project time horizon with the value '0'.

FILLMISSING('Input node',"ProductDimension", 1) =

Year	Product	Value
2021	Car	100
2021	Van	1
2021	Truck	1
2023	Car	1
2023	Van	90
2023	Truck	1

With this formula the input node is filled up with the missing level values of the Dimension 'ProductDimension' (Truck).

The entered value is '1'.

Example with different behaviors

Input node=

Year	Product	Value
2022	Car	100
2023	Van	200
2024	Car	300

FILLMISSING('Input node', "Time", "1", "All") =

Year	Product	Value
2021	Car	1
2022	Car	100
2023	Car	1
2024	Car	300
2021	Van	1
2022	Van	1
2023	Van	200
2024	Van	1

When using default behaviour "All", the function acts the same way as the previous example and fills the empty values over the project horizon.

FILLMISSING('Input node', "Time", "1", "StartAtFirst") =

Year	Product	Value
2022	Car	100
2023	Car	1
2024	Car	300
2023	Van	200
2024	Van	1

When using the behaviour "StartAtFirst", the function fills the empty entries starting after the first value present for each product. Filling ends at the end time of project horizon.

FILLMISSING('Input node', "Time", "1", "FinishAtLast") =

Year	Product	Value
2021	Car	1
2022	Car	100
2023	Car	1
2024	Car	300
2021	Van	1
2022	Van	1
2023	Van	200

With using the behavior "FinishAtLast", the function fills the empty values until the last entry present for each product. The filling starts at the start time of project horizon.

FILLMISSING('Input node', "Time", "1", "Interval") =

Year	Product	Value
2022	Car	100
2023	Car	1
2024	Car	300
2023	Van	200

When using the behavior "Interval", the function fills the empty values starting at the first entry present and finishes at the last entry present for each product.

Example with linked levels

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

Input =

Month	CalendarMonth	Value
2021-02	06	100
2021-03	06	105
2021-04	11	110
2021-05	05	115
2021-06	06	120
2021-07	02	125

FILLMISSING('Input',"TimeDimension", 15) =

Month	CalendarMonth	Value
2021-01	01	15
2021-02	02	100
2021-03	03	105
2021-04	04	110
2021-05	05	115
2021-06	06	120
2021-07	07	125
2021-08	08	15
2021-09	09	15
2021-10	10	15
2021-11	11	15
2021-12	12	15