Skip to main content
Skip table of contents

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.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.