lag of a value

Hello,

Currently have similar dataset to this:
ID_1 ID_2 ID_3 Year Value
a b c 2018 3
a b c 2017 4
a b c 2016 5
a b z 2018 5
a b z 2017 7
a b z 2016 6

Would like to calculate 3 new columns:

Column a) Mean of group based on ID_1 and ID_3
4
4
4
6
6
6

Column b) average for all values of similar groups based on ID1 ID2 ID3 but only for prior years:
Result of : (3+4+5)/3
Result of : (4+5)/2
Result of : 3
Result of : (5+7+6)/3
Result of : (7+6)/2
Result of : 6

Column c) lag = value for same id previous year
4
5
NA
7
6
NA

Thanks a lot for the help!
Btw, Knime is awesome!

Bernard

I built an example that creates the three new values. It relies heavily on Loops and you might have to check if the logic of the rules is correctly implemented. You might change that as you need it. I inserted a new artificial ID to later join results back and preserve the original order.

If I understood your example correctly there is one value that should be a 5 instead of 3. But you could manipulate the settings accordingly.

I used this example to create the cumulative sum:

kn_example_lag_3_new_values.knwf (132.4 KB)

1 Like

Amazing. This looks perfect!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.