FINDFIRST and Populate
Imagine your node contains the data of the Product Contribution for the particular products. If there is no value present, you want to use the first existing value. Let's say you need the Data of 2018.
Node 'Profit Contribution'
Profit Contribution in % | 2016 | 2017 | 2018 | 2019 |
|---|---|---|---|---|
Product A* | 4,5 | 4,5 | ||
Product B | 5 | 6 | 6,4 | |
Product C* | 7 | |||
Product D | 5,5 | 5,6 |
*Project A and C have no values in 2018
First, you extrapolate the existing data for the following years, followed by setting the defined values to 1.
After you add this up by using RUNNINGSUM.
ROLLFORWARD('Profit Contribution')
Profit Contribution in % | 2016 | 2017 | 2018 | 2019 |
|---|---|---|---|---|
Product A | 4,5 | 4,5 | 4,5 | 4,5 |
Product B | 5 | 6 | 6,4 | |
Product C | 7 | |||
Product D | 5,6 | 5,6 |
TRUE(ROLLFORWARD('Profit Contribution')
Profit Contribution in % | 2016 | 2017 | 2018 | 2019 |
|---|---|---|---|---|
Product A | 1 | 1 | 1 | 1 |
Product B | 1 | 1 | 1 | |
Product C | 1 | |||
Product D | 1 | 1 |
RUNNINGSUM(TRUE(ROLLFORWARD('Profit Contribution'))
Profit Contribution in % | 2016 | 2017 | 2018 | 2019 |
|---|---|---|---|---|
Product A | 1 | 2 | 3 | 4 |
Product B | 1 | 2 | 3 | |
Product C | 1 | |||
Product D | 1 | 2 |
leads to:
IF(RUNNINGSUM(TRUE(ROLLFORWARD('Profit Contribution')))=1,'Profit Contribution')
To use the value of the original node, matching with the above shown fields, whose RUNNINGSUM operations provide 1, in case no value is present you say:
IF(IS_NA ('Profit Contribution'), EXPAND(DROPLEVEL(IF(RUNNINGSUM(TRUE(ROLLFORWARD('Profit Contribution')))=1, 'Profit Contribution'),"Year"),"Year"), 'Profit Contribution').
By adding DROPLEVEL and EXPAND the selected values are now defined for the years.
For 2018 and in total we get:
Profit Contribution in % | 2016 | 2017 | 2018 | 2019 |
|---|---|---|---|---|
Product A | 4,5 | 4,5 | 4,5 | 4,5 |
Product B | 5 | 5 | 6 | 6,4 |
Product C | 7 | 7 | 7 | 7 |
Product D | 5,5 | 5,5 | 5,5 | 5,6 |