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 |
|
Limitations |
|
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.