Multi row formula

nkt

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

nkt

Solved this by using the Window recipe, sorting on Column A and selecting the Min of Value. Make sure you do not select the Window frame option (in the Windows definition screen).