Hi all,
I have the following dataset (in real life 100+ rows):
| Column A | Value |
| 1 | 100 |
| 2 | 80 |
| 3 | 85 |
| 4 | 82 |
| 5 | 70 |
I would like to correct the Values column so it is always decreasing, taking the value of the previous row if the value has increased. So the table should be corrected to this:
| Column A | Value |
| 1 | 100 |
| 2 | 80 |
| 3 | 80 |
| 4 | 80 |
| 5 | 70 |
To achieve this I would like to use the formula targeting multiple rows:
if (Value] > [Row-1:Value]) AND !isnull([Row-1:Value]) then [Row-1:Value] else [Value] endif
How can I achieve this?
I tried the window recipe to determine the lag difference (difference with the previous row), remove the positive lag difference values and fill the empty cells with the previous value. However, this only works when you look 1 row back, so the example row #4 would not be corrected. I can look back multiple rows, however this generates also multiple columns in the windows recipe. For a dataset with 100+ rows, this means 100+ additional columns. This doesn't feel like the best method.
Hopefully you know a better method!
Thank you in advance.
Best,
Nick
