Skip to main content
Skip table of contents

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

JavaScript errors detected

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

If this problem persists, please contact our support.