FINDFIRST

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

(warning) 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



Was this helpful?

Yes | Somewhat | No