I need to calculate the cumulative (adjusted) product over a table, whereby I need to include a value from the previous row into the calculation.
Example:
| _time | a | b |
|---|---|---|
| 01.01.2020 | 100’000 | - |
| 02.01.2020 | 110’000 | - |
| 03.01.2020 | 210’000 | 100’000 |
| 04.01.2020 | 189’000 | - |
| 05.01.2020 | 139’000 | -50’000 |
| 06.01.2020 | 145’950 |
I need to calculate c based on the following formula:
c = a / (previousA +b)
Where previousA is the a value from the previous row. So for the ‘02.01.2020’ row a is 110’000 and previousA is 100’000.
This should result in the following:
| _time | a | b | c |
|---|---|---|---|
| 01.01.2020 | 100’000 | - | |
| 02.01.2020 | 110’000 | - | 1.1 |
| 03.01.2020 | 210’000 | 100’000 | 1 |
| 04.01.2020 | 189’000 | - | 0.9 |
| 05.01.2020 | 139’000 | -50’000 | 1 |
| 06.01.2020 | 145’950 | 1.05 |
I cannot figure out how to get a value from the previous row.
I tried both map which performs an operation on each row, but I could not figure out a way how to access a value from the previous row. And I tried reduce, where I can use the accumulator to keep the calculated value c but it gives me one value for the entire table only and not one value for each row.
