Get value from a previous row

[Former Member]

Is there a way to get a value from a previous row, not necessarily the one just before??

For example:

item1 A 23

item2 B 96 <--

item3 A 10

item4 A 47

item5 B 0 <--

When the value in the 3rd column is 0, I want to return the value of the item that has a like 2nd column value (B). In this case it would return 96.

Is this possible??

Thanks in advance for your help.

[Former Member]

Hi John,

There is a previous function, in XI 3.0 not sure about XI r2, but that will only return the previous line. You can nest them, as in

previous(previous([My Object]))

I have never tried in production, so I am not sure how far you can go, that is the number of nested statements.

Let me know how you get on

Regards

Alan

[Former Member]

Alan,

Thanks for the response but unfortunately that won't work. There is no telling where in the report where the zero value and the same column B value may be. Also using Previous doesn't take into account the value of column B.

Thanks

John

[Former Member]

Hi John,

Sorry about that - you would have to sort by the second column, then the logic would be if([Column 3]=0;Previous([Column 3]);[Column 3])

You might look at input and output report context.

Finally there is a last method.

Hope this helps

Alan

[Former Member]

Alan,

Thanks again but I need the report to be sorted by column 1.

Is there a way to save the column 3 value based on what column 2 is and then use that saved value when column 3 is zero??

Thanks

[Former Member]

Hi John,

I am not sure if there is anything. I had a look at a couple of things.

The closest was

=Last([Revenue] ) In ([Service Line]) Where ([Revenue] > 0)

but I don't think this is what you want.

Regards

Alan