Multi row formula

nkt
nkt Registered Posts: 4 ✭✭✭

Hi all,

I have the following dataset (in real life 100+ rows):

Column AValue
1100
280
385
482
570

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 AValue
1100
280
380
480
570

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

Tagged:

Best Answer

  • nkt
    nkt Registered Posts: 4 ✭✭✭
    Answer ✓

    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).

Setup Info
    Tags
      Help me…