ROLLFORWARD_ADVANCED

In Rollforward & time series

Every parameter after input is optional and positional. To set a later one without the earlier ones, use the custom parameter order.

The ROLLFORWARD_ADVANCED function projects a node into the future using a list of provided drivers, combining the functionality of ROLLFORWARD and ROLLFORWARD_MUL into one function with additional controls.

Unlike ROLLFORWARD, which rolls the last available value of each level value forward regardless of time period, ROLLFORWARD_ADVANCED looks at a specific time period and projects the data available there into the future.

Use this function when you need more control over a projection than ROLLFORWARD offers, for example driver combination mode, window aggregation, or aging levels: ROLLFORWARD_ADVANCED('Sold Ice Cream', 'New Locations', 'Marketing Activities').

Common patterns

Parameters

'input'

Type: Node reference · Required: Yes · Default: —

The input with values that are to be projected.

'drivers'

Type: List of node references · Required: No · Default: None (no drivers)

A list of nodes that will be applied as drivers. Relative drivers are determined by using the percentage unit on the node. Multiple drivers can, but do not need to, be placed in square brackets (this is mandatory when using a custom parameter order, see below).

Every absolute driver must have the same levels as input; every relative driver must have the same levels, or a subset of the levels, of those on input.

"time"

Type: Level name · Required: No · Default: Lowest available time level of input

The level that is used to project the input. The function calculates the projected values in a loop over the time level, calculating each value based on the value of the previous loop. For some use cases, this level can also be a non-time-dimensional level.

The level must be available on input; it is required if input has no time level; and it must be the most granular level from its dimension on input.

"start"

Type: Level value · Required: No · Default: The time level value from input incremented by one

The level value from time that the projection will begin at. Any value in input that comes before the start level value is left unchanged. Must be a value from time, and must come before end within the time value dimension (this includes taking into consideration a higher level in the dimension than the time value).

"end"

Type: Level value · Required: No · Default: The end of the horizon

The level value from time that the projection will end at. Any value in input that comes after the end level value is left unchanged. Must be a value from time, and must come after start within the time value dimension.

windowSize

Type: Number · Required: No · Default: 1

The number of previous values used for the loop calculation. The calculation is an aggregation of all values in the window using the provided windowFunction. For example, a windowSize of 1 means the current value is calculated only using the last previous value, while a size of 3 uses the previous 3 values. A window value will not be calculated until it is full. Must be 1 or larger.

"windowFunction"

Type: Keyword · Required: No · Default: "FIRST"

The aggregation function to apply to the values within the window to calculate a value for each time iteration.

• "FIRST": the first value in the window. This is the default.
• "LAST": the last value in the window.
• "AVG": the average of the values in the window.
• "SUM": the sum of all values in the window.
• "PROD": the product of all values in the window.

"windowType"

Type: Keyword · Required: No · Default: "ROLLING"

Determines whether the values in the window to which drivers are applied are updated while looping.

• "FIXED": the projection is always based on the initially defined window while looping.
• "ROLLING": while looping, the window that the projection is based on moves with the projection. This is the default.

"mode"

Type: Keyword · Required: No · Default: "ADDITIVE"

Determines how drivers are applied.

• "ADDITIVE": each driver is applied additively in the provided order, with no cross effects between them. This is the default.
• "MULTIPLICATIVE": drivers are applied in the provided order one after another. Every driver is applied on the base including all previous drivers, so it can trigger cross effects between drivers.

"agingLevels"

Type: List of level names · Required: No · Default: None (no aging levels)

A list of levels from input that are incremented/shifted with every loop iteration along the time value. For example, this can be used to increment a contract age every year when iterating over the years as the time value. Multiple levels can, but do not need to, be placed in square brackets (this is mandatory when using a custom parameter order, see below).

Each agingLevel must be on input; must be the most granular level from its dimension on input; and cannot be from the same dimension as time or another agingLevel. This function always makes the links between the input and agingLevel dimensions match the dimension management. Do not use it if you want the node to have different level links than the dimensions.

agingOffset

Type: Number · Required: No · Default: -1

The offset by which every agingLevel is incremented/shifted with every loop iteration. Follows the same format as the SHIFT function, where -1 increments the agingLevel by one value. Must be -1 or less.

"linkedLevelsAggregation"

Type: Keyword · Required: No · Default: "LAST"

Defines which values to select in cases where levels and linked-to-levels are not mapped to each other as specified in the dimension management (“ambiguous mapping”). Only applied if the incorrect links relate to a level within the same dimension as time or an agingLevel. The available aggregation types apply.

Output shape

Dimensionality

Same as input. When agingLevels are used, their links to other dimensions are set to match dimension management.

Values

Values before start are left unchanged. From start to end, each period on the time level is projected from the window of prior values, with drivers applied according to mode.

Row count

Extended to cover the projection range on the time level.

Watch out

Custom parameter order

Instead of following the standard order, you can pass parameters by name. This lets you set any parameter without naming the ones before it. You can also mix the two, with positional parameters first, then named ones.

How to use it

• Name each parameter before its value, e.g. ROLLFORWARD_ADVANCED(windowSize = 3, input = 'Revenue').
• Use the names from the Parameters section above (case does not matter).
• Do not quote the parameter name. Values keep their usual quoting: single quotes for nodes, double quotes for level names and values, no quotes for numbers.
• Separate the name and value with =, optionally surrounded by spaces or line breaks.
• A drivers or agingLevels list with more than one value must be in square brackets, so the parser knows where the list ends: drivers = ['driver1', 'driver2']. A single value needs no brackets: drivers = 'driver1'.

Limitations

• input is always required, regardless of order.
• Once you name a parameter, every parameter after it must be named too.
• Do not specify the same parameter twice.
• If a parameter is given both positionally and by name, the named value wins.

Example

ROLLFORWARD_ADVANCED('ExpandOneOnYearNode', mode = "MULTIPLICATIVE", windowFunction = "AVG", windowSize = 2)

Only mode, windowFunction, and windowSize are set here; every other parameter uses its default.

Examples

All examples use a time horizon of 2025 to 2031.

ROLLFORWARD_ADVANCED with no optional parameters

Input node: 'Sold Ice Cream'

Formula: ROLLFORWARD_ADVANCED('Sold Ice Cream')

ROLLFORWARD_ADVANCED with an absolute driver

Absolute driver: 'New Locations'

Formula: ROLLFORWARD_ADVANCED('Sold Ice Cream', 'New Locations')

ROLLFORWARD_ADVANCED with a relative driver

Relative driver: 'Marketing Activities' (unit = Percent)

Formula: ROLLFORWARD_ADVANCED('Sold Ice Cream', 'Marketing Activities')

ROLLFORWARD_ADVANCED with start and end configured

All previous examples use the default start of 2027 as the input data ends in 2026, and end of 2031 as this is the end of the horizon.

This example shows how the range can be changed.

Formula: ROLLFORWARD_ADVANCED('Sold Ice Cream', [], "Year", "2027", "2029")

ROLLFORWARD_ADVANCED with windowSize configured

This example sets windowSize to 2 instead of the default 1, so each value is calculated from the two previous values.

This functionality can be used to project data while taking seasonality into account.

Formula: ROLLFORWARD_ADVANCED('Sold Ice Cream', [], "Year", "2027", "2031", 2)

Example for seasonality over the time level Quarters

Input node: 'Sold Ice Cream in Quarters'

Formula: ROLLFORWARD_ADVANCED('Sold Ice Cream in Quarters', [], "Quarter", "2027-Q1", "2028-Q4", 4)

ROLLFORWARD_ADVANCED with windowFunction configured

This example uses windowFunction "SUM" instead of the default "FIRST", summing the values in the window instead of taking the first one.

Formula: ROLLFORWARD_ADVANCED('Sold Ice Cream', [], "Year", "2027", "2029", 2, "SUM")

The remaining parameters (windowType, mode, agingLevels, agingOffset, linkedLevelsAggregation) are omitted from this formula and use their default values.

ROLLFORWARD_ADVANCED with windowType configured

This example uses windowType "FIXED" instead of the default "ROLLING", so the projection stays based on the initially defined window instead of moving with it.

Absolute driver: 'Additional Sales'

Formula: ROLLFORWARD_ADVANCED('Sold Ice Cream', 'Additional Sales', "Year", "2027", "2029", 1, "FIRST", "FIXED")

The remaining parameters (mode, agingLevels, agingOffset, linkedLevelsAggregation) are omitted from this formula and use their default values.

ROLLFORWARD_ADVANCED with mode configured

This example uses mode "MULTIPLICATIVE" instead of the default "ADDITIVE", so each driver compounds on the base including all previous drivers.

This functionality can be used to project data while taking interest rates into account.

Input node: 'Account Balance (Base)'

Absolute driver: 'Deposit'

Relative driver: 'Additional Contributions' (unit = Percent)

Formula: ROLLFORWARD_ADVANCED('Account Balance (Base)', 'Deposit', 'Additional Contributions', "Year", "2027", "2031", 1, "FIRST", "ROLLING", "MULTIPLICATIVE")

ROLLFORWARD_ADVANCED with agingLevels configured

This example adds an agingLevels configuration, incrementing Contract Age with every loop iteration instead of leaving it unchanged.

This functionality can be used to project data while taking contract aging into account.

Input node: 'Contracts'

Absolute driver: 'Additional Contracts'

Relative driver: 'Churn Rate'

Formula: ROLLFORWARD_ADVANCED('Contracts', 'Additional Contracts', 'Churn Rate', "Year", "2028", "2031", 1, "FIRST", "ROLLING", "MULTIPLICATIVE", ["Contract Age"])

The square brackets are optional here, so this also works:

Equivalent to: ROLLFORWARD_ADVANCED('Contracts', 'Additional Contracts', 'Churn Rate', "Year", "2028", "2031", 1, "FIRST", "ROLLING", "MULTIPLICATIVE", "Contract Age")

ROLLFORWARD_ADVANCED with agingOffset configured

This example uses an agingOffset of -2 instead of the default -1, shifting agingLevel by two values per loop instead of one.

Input node: 'Contracts'

Absolute driver: 'Additional Contracts'

Relative driver: 'Churn Rate'

Formula: ROLLFORWARD_ADVANCED('Contracts', 'Additional Contracts', 'Churn Rate', "Year", "2028", "2031", 1, "FIRST", "ROLLING", "MULTIPLICATIVE", ["Contract Types"], -2)

ROLLFORWARD_ADVANCED with linkedLevelsAggregation configured

Use this parameter when levels are mapped ambiguously. The example below shows what ambiguous mapping is and how the parameter resolves it.

The initial link between levels in the dimension management might look like this:

In the unlikely case of ambiguous mapping, which could appear due to the effects of certain formula combinations, the data preview of a node might look like this:

Due to an incorrect link between levels compared to the dimension management, this state is called “ambiguous mapping”.

Different linkedLevelsAggregation settings let you choose which values are used.

The default "LAST" produces:

See also

ROLLFORWARD

When you need a straightforward additive projection from the last available value, without window, timing, or aging controls.

ROLLFORWARD_MUL

When you need a straightforward multiplicative projection from the last available value, without the additional controls.

PREVIOUS

When you need previous-period loop logic with an explicit base case rather than a full driver-based projection.