ROLLFORWARD_ADVANCED
AVAILABLE SINCE 6.0
The ROLLFORWARD_ADVANCED has more functionality compared to the ROLLFORWARD with the advantage of additional parameters that allow for a more advanced projection of your data.
These parameters are described in detail below.
To learn more about the ROLLFORWARD, please look at the formulas' documentation page where the basic use case of the ROLLFORWARD with one input and 1-3 drivers is shown.
While the ROLLFORWARD rolls the last value of each level value (no matter the time period) forward, the ROLLFORWARD_ADVANCED looks at one specific time period and projects the there available data of each level value into the future.
Overview
Description | The ROLLFORWARD_ADVANCED allows to project a node into the future with a list of provided drivers, both relative and absolute. The function has optional parameters: aging levels, which can be offsetted on each loop of the projection; the setting of start and end values for the projection; window settings that allow a selection of previous values to be used for a loop calculation. |
Technical Signature | ROLLFORWARD_ADVANCED(input[, [drivers] [, time [, start, end [, windowSize [, windowFunction [, windowType [, mode [, [agingLevels] [, agingOffset] [, linkedLevelsAggregation]]]]]]]]]]) |
Parameters | Mandatory parameters
Optional parameters
|
Limitations |
|
Custom Parameter Order | The ROLLFORWARD_ADVANCED can additionally be specified with a custom parameter order. That offers the possibility to specify the parameters by their names without following the standard order described above. This enables a deviation from the default for any parameter within the function without naming all prior parameters. A combination of parameters by the standard order followed by a custom parameter order is possible as well. How to:
Limitations:
Example node operation: ROLLFORWARD_ADVANCED(‘ExpandOneOnYearNode’, mode = “MULTIPLICATIVE”, windowFunction = “AVG”, windowSize = 2)
An ‘input’ is mandatory to be provided. |
Examples
Note all examples are with a time horizon of 2017 - 2023
ROLLFORWARD_ADVANCED with no optional parameters
Nodes:
input: Sold Ice Cream
Flavour | 2017 | 2018 |
---|---|---|
Lemon | 95 | 90 |
Cookies | 100 |
ROLLFORWARD_ADVANCED('input')
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 90 | 90 | 90 | 90 | 90 |
Cookies | 100 | 100 | 100 | 100 | 100 | 100 |
ROLLFORWARD_ADVANCED with an absolute driver
Nodes:
AbsoluteDriver: New Locations
Flavour | 2019 | 2022 | 2023 |
---|---|---|---|
Lemon | 50 | 100 | |
Cookies | 200 |
Outputnode: ROLLFORWARD_ADVANCED('input', 'AbsoluteDriver')
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 90 + 50 = 140 | 140 | 140 | 140 | 140 + 100 = 240 |
Cookies | 100 | 100 | 100 | 100 | 100 + 200 = 300 | 300 |
ROLLFORWARD_ADVANCED with a relative driver
Nodes:
RelativeDriver (unit = ‘Percent’): Marketing Activities
2019 | 2022 | 2023 |
---|---|---|
0.02 | 0.1 | 0.2 |
Outputnode: ROLLFORWARD_ADVANCED('input', 'RelativeDriver')
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 90 + (90 * 0.02) = 91,8 | 91,8 | 91,8 | 91,8 + (91,8 * 0.1) = 100,98 | 100,98 + (100,98 * 0.2) = 121,18 |
Cookies | 100 | 100 + (100 * 0.02) = 102 | 102 | 102 | 102 + (102 * 0.1) = 112,2 | 112,2 + (112,2 * 0.2) = 134,64 |
ROLLFORWARD_ADVANCED with ‘start’ and ‘end’ configured
All previous examples use the default ‘start’ of 2019 as the input data ends in 2018, and ‘end’ of 2023 as this is the end of the horizon.
This example shows how the range can be changed.
ROLLFORWARD_ADVANCED('input', [], "Year", "2019", "2021")
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 |
---|---|---|---|---|---|
Lemon | 95 | 90 | 90 | 90 | 90 |
Cookies | 100 | 100 | 100 | 100 |
ROLLFORWARD_ADVANCED with ‘windowSize’ configured
All previous examples use the default ‘windowSize’ of 1.
This example shows how changing this parameter can affect the calculations.
This functionality can be used to project data while taking seasonality into account.
Outputnode: ROLLFORWARD_ADVANCED('input', [], "Year", "2019", "2023", 2)
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 95 | 90 | 95 | 90 | 95 |
Cookies | 100 | 100 | 100 |
Example for seasonality over the time level ‘Quarters’
input: Sold Ice Cream in Quarters
Flavour | 2018 | |||
2018-Q1 | 2018-Q2 | 2018-Q3 | 2018-Q4 | |
Lemon | 15 | 30 | 30 | 15 |
Cookies | 20 | 30 | 30 | 20 |
Outputnode: ROLLFORWARD_ADVANCED('input', [], "Quarter", "2019-Q1", "2020-Q4", 4)
Flavour | 2018 | 2019 | 2020 | |||||||||
2018-Q1 | 2018-Q2 | 2018-Q3 | 2018-Q4 | 2019-Q1 | 2019-Q2 | 2019-Q3 | 2019-Q4 | 2020-Q1 | 2020-Q2 | 2020-Q3 | 2020-Q4 | |
Lemon | 15 | 30 | 30 | 15 | 15 | 30 | 30 | 15 | 15 | 30 | 30 | 15 |
Cookies | 20 | 30 | 30 | 20 | 20 | 30 | 30 | 20 | 20 | 30 | 30 | 20 |
ROLLFORWARD_ADVANCED with ‘windowFunction’ configured
All previous examples use the default ‘windowFunction’ "FIRST".
This example shows how changing this parameter can affect the calculations.
ROLLFORWARD_ADVANCED('input', [], "Year", "2019", "2021", 2, "SUM")
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 95 + 90 | 90 + 185 | 185 + 275 | 275 + 460 | 460 + 735 |
Cookies | 100 | 0+100 | 100 + 100 | 100 + 200 | 200 + 300 | 300 + 500 |
NOTE
'…' uses default values.
ROLLFORWARD_ADVANCED with ‘windowType’ configured
All previous examples use the default ‘windowType’ "ROLLING".
This example shows how changing this parameter can affect the calculations.
AbsoluteDriver: Additional Sales
Flavour | 2019 | 2020 |
---|---|---|
Lemon | 50 | 10 |
Cookies | 200 |
ROLLFORWARD_ADVANCED('input', ‘Absolute Driver’, "Year", "2019", "2021", 1, "FIRST", "FIXED")
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 |
---|---|---|---|---|---|
Lemon | 95 | 90 | 90 +50 = 140 | 90 +10 = 100 | 90 |
Cookies | 100 | 100 | 100 +200 = 300 | 100 |
NOTE
'…' uses default values.
ROLLFORWARD_ADVANCED with ‘mode’ configured
All previous examples use the default ‘mode’ "ADDITIVE".
This example shows how changing this parameter can affect the calculations.
This functionality can be used to project data while taking interest rates into account.
input: Account Balance (Base)
2017 | 2018 | |
---|---|---|
Account Balance | 95 | 90 |
AbsoluteDriver: Deposit
2019 | 2022 | 2023 | |
---|---|---|---|
Deposits | 50 | 100 |
RelativeDriver (unit = ‘Percent’): Additional Contributions
2019 | 2022 | 2023 | |
---|---|---|---|
Contributions | 0.02 | 0.1 | 0.2 |
Outputnode: ROLLFORWARD_ADVANCED('input', 'AbsoluteDriver', 'RelativeDriver', "Year", "2019", "2023", 1, "FIRST", "ROLLING", "MULTIPLICATIVE")
2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | |
---|---|---|---|---|---|---|---|
Account Balance | 95 | 90 | (90 + 50) * (1 + 0.02) | 142,8 | 142,8 | (142,8 + 0) * (1 + 0.1) | (157,08 + 100) * (1 + 0.2) |
ROLLFORWARD_ADVANCED with ‘agingLevels’ configured
All previous examples use no ‘agingLevel’ configuration.
This example shows how changing this parameter can affect the calculations.
This functionality can be used to project data while taking contract aging into account.
Nodes:
input2: Contracts
Contract Age | 2017 | 2018 | 2019 |
---|---|---|---|
1 | 95 | 90 | 70 |
2 | 100 | 90 | |
3 | 150 |
AbsoluteDriver2: Additional Contracts
Contract Age | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|
1 | 50 | |||
2 | 100 | |||
3 | 600 | 200 |
RelativeDriver2: Churn Rate
2020 | 2022 | 2023 |
---|---|---|
-0.02 | -0.1 | -0.2 |
Outputnode: ROLLFORWARD_ADVANCED('input2', 'AbsoluteDriver2', 'RelativeDriver2', "Year", "2020", "2023", 1, "FIRST", "ROLLING", "MULTIPLICATIVE", ["Contract Age"])
Contract Age | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
1 | 95 | 90 | 70 | 50 * (1 + (-0.02)) | |||
2 | 100 | 90 | 70 * (1 + (-0.02)) | 49 | 100 * (1 + (-0.1)) | ||
3 | 150 | 90 * (1 + (-0.02)) | 68.6 + 600 | 49 * (1 + (-0.1)) | (90 + 200) * (1 + (-0.2)) |
ROLLFORWARD_ADVANCED with ‘agingOffset’ configured
The previous example use the default ‘agingOffset’ configuration of -1.
This example shows how changing this parameter can affect the calculations.
input3: Contracts
Contract Types | 2017 | 2018 | 2019 |
---|---|---|---|
A | 95 | 90 | 70 |
B | 100 | 90 | |
C | 150 |
AbsoluteDriver3: Additional Contracts
Contract Types | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|
A | 50 | |||
B | 100 | |||
C | 600 | 200 |
RelativeDriver3: Churn Rate
2020 | 2022 | 2023 |
---|---|---|
-0.02 | -0.1 | -0.2 |
Outputnode: ROLLFORWARD_ADVANCED('input3', 'AbsoluteDriver3', 'RelativeDriver3', "Year", "2020", "2023", 1, "FIRST", "ROLLING", "MULTIPLICATIVE", ["Contract Types"], -2)
Contract Types | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
A | 95 | 90 | 70 | 50 * (1 + -0.02) | |||
B | 100 | 90 | 100 * (1 + -0.1) | ||||
C | 150 | 70 * (1 + -0.02) | 49 + 600 | 200 * (1 + -0.2) |
ROLLFORWARD_ADVANCED with ‘linkedLevelsgAgregation’ configured
This parameter should be used in cases of levels being mapped ambiguously. The following example will show what “ambiguous mapping” is and how this parameter resolves this.
The initial link between levels in the dimension management might look like this:
Linked-from Level | Linked-to Level |
---|---|
Linked-from level 1 | Linked-to level 1 |
Linked-from level 2 | Linked-to level 2 |
Linked-from level 3 | Linked-to level 3 |
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:
Linked-to level | Linked-from level | 2023 |
Linked-to level 1 | Linked-from level 1 | 1 |
Linked-from level 2 | 1 | |
Linked-from level 3 | 1 | |
Linked-to level 2 | Linked-from level 1 | 2 |
Linked-from level 2 | 2 | |
Linked-from level 3 | 2 | |
Linked-to level 3 | Linked-from level 1 | 3 |
Linked-from level 2 | 3 | |
Linked-from level 3 | 3 |
Due to an incorrect link between levels compared to the dimension management, this state is called “ambiguous mapping”.
By using different settings for the ‘linkedLevelsAggregation’ the user is able to define which values should be selected for the calculation.
The default configuration LAST would in this case, result in the following output:
Linked-to Level | Linked-from Level | 2023 |
Linked-to level 1 | Linked-from level 1 | 3 |
Linked-to level 2 | Linked-from level 2 | 3 |
Linked-to level 3 | Linked-from level 3 | 3 |
Potential Use Cases
Potential Use Case | Which Parameters | How to use it? |
---|---|---|
Seasonal Effects | time | This parameter defines the level that will be used for the calculation of the loop for the projection. This could be fore example months, quarters or years. |
start | The 'start' parameter defines the start of the projection period. | |
end | The 'end' parameter defines the end of the projection period. | |
windowSize | The ‘windowSize’ parameter defines the number of previous values that should be used for the calculation. For the seasonal effects, that could be the previous months that should be taken into account to represent the seasonality. | |
Loop Calculations | time | This parameter defines the level that will be used for the calculation of the loop for the projection. This could be fore example months, quarters or years. |
start | The 'start' parameter defines the start of the projection period. | |
end | The 'end' parameter defines the end of the projection period. | |
Aging | time | This parameter defines the level that will be used for the calculation of the loop for the projection. This could be fore example months, quarters or years. |
start | The 'start' parameter defines the start of the projection period. | |
end | The 'end' parameter defines the end of the projection period. | |
agingLevel | The ‘agingLevel' parameter defines which level(s) are incrementally shifted along the 'time’. For the aging use case, that could for example be the level ContractAge. | |
agingOffset | The “agingOffset” parameter defines with which offset a level is shifted with every loop along the projection. |