FILLMISSING_LAST

Basic Overview

Description	Fills any missing level values using the last available value from the specified dimension in the input node. If a row does not have a previous value, the value in the 'Value' parameter will be used. The input node should already contain the levels of the dimension. If no dimension is specified, the default dimension to be filled is 'Time'.
Signature	FILLMISSING_LAST('Node' [, "Dimension" [, "Value" [, "Behavior"]]])
Parameters	Node: A node (with potentially missing level values) Name of the dimension whose level values will be used to fill the input node. This function does not add levels to the input; it only fills missing level values of the levels already present. The default fill is done at the lowest time level. The value to be used if there is no last available value on the lowest level of the dimension. The default value is 0. Behavior: [optional] The filling behavior that the function will follow. Available behaviors are: All: Default behavior. Fills all empty values with the specified value where there is no last value available. StartAtFirst: Fills only the empty values, starting after the first entry of the specified dimension, where there is no last value available. FinishAtLast: Fills only the empty values until the last entry, where there is no last value available. Interval: Fills only the empty entries with the specified value between the first and last present values.
Limitations	The node must already contain levels of the specified dimension If the node contains linked levels, it is not possible to fill values for the dimension that is being linked to. Instead, fill the values for the dimension that is being linked from to obtain all valid values. Important: This function ensures that the links from the levels of the specified dimension match those in the dimension management. Do not use this function if you want the node to have different level links than the dimensions.

Example 1:

The time horizon of the project is 2020-2024.

The input node features the level values “2021”, “2023”.

Input node =

Year	Value
2021	10
2023	50

FILLMISSING_LAST('Input node') =

Year	Value
2020	0
2021	10
2022	10
2023	50
2024	50

For this input, the function starts filling from the first available entry which is in this case “2021”. Therefore the value 10 is filled until the next available value which is in “2023”. From “2023“ value 50 is used to fill the rest. The beginning which is “2020“ is filled with the default value which is in this case 0.

FILLMISSING_LAST('Input node', “Time“, 0, “STARTATFIRST“) =

Year	Value
2021	10
2022	10
2023	50
2024	50

The behaviour “STARTATFIRST“ will produce a similar output, however, starting from the first available value which is “2021“.

FILLMISSING_LAST('Input node', “Time“, 0, “FINISHATLAST“) =

Year	Value
2020	0
2021	10
2022	10
2023	50

The behavior “FINISHATLAST“ will fill until the last value for the dimension which is “2023 - 50”.

FILLMISSING_LAST('Input node', “Time“, 0, “INTERVAL“) =

Year	Value
2021	10
2022	10
2023	50

“INTERVAL“ will apply both constraints from the previous examples which will limit the filling between the first and the last available value.

Example 2:

The time horizon of the project is 2020-2024.

The dimension “ProductDimension” consists of the level “Product” and the level values “A”, “B”, “C”, “D“, and “E“.

The input node features the level values “2021”, “2023” and “Car”, “Truck”.

Input node =

Year	Product	Value
2021	B	10
2023	C	50
2023	E	100

FILLMISSING_LAST('Input node', “ProductDimension“) =

Year	Product	Value
2021	A	0
2021	B	10
2021	C	10
2021	D	10
2021	E	10
2023	A	0
2023	B	0
2023	C	50
2023	D	50
2023	E	100

Function can be applied to a non-time dimension. In this example the dimension “ProductDimension” is used and the filling is done according to “Product” level. Keeping the years 2021 and 2023 constant, all the values of “Product” level is filled with the first available value which is appearing at “B” with the value 10. Since there is no other entry (for the year 2021) for any other product, the rest is filled with this value. For year 2023 the first “Product” value is appearing at “C“ which means the value 50 will be used until the next entry which is appearing at “E” with 100. Similarly to previous examples any row outside the first available value is filled with the default value 0.

Similar to the different behaviours on Example 1:

FILLMISSING_LAST('Input node', “ProductDimension“, 0, “STARTATFIRST“) =

Year	Product	Value
2021	B	10
2021	C	10
2021	D	10
2021	E	10
2023	C	50
2023	D	50
2023	E	100

“STARTATFIRST” parameter will tell the function to start filling from the first available value.

FILLMISSING_LAST('Input node', “ProductDimension“, 0, “FINISHATLAST“) =

Year	Product	Value
2021	A	0
2021	B	10
2023	A	0
2023	B	0
2023	C	50
2023	D	50
2023	E	100

“FINISHATLAST” parameter will tell the function to stop filling at the last available value.

FILLMISSING_LAST('Input node', “ProductDimension“, 0, “INTERVAL“) =

Year	Product	Value
2021	B	10
2023	C	50
2023	D	50
2023	E	100

“INTERVAL” parameter will tell the function to fill values between the first and last available values. For the year 2023 this interval is between C and E (C, D, E). Note that for the year “2021” there is no interval therefore, the value “B” will appear alone.