Multi-Row Formula, go back X Rows

[DonButeyn]

Hi,

I'm looking for a way to go back X rows in the multi-row formula based on a value in the data table.

For example, the [Duration] is an integer 70 in one row, I want to reference 70 rows back, instead of a constant -1 or -2 rows.

Thanks!

Donny

[rituraj963 · Accepted solution]

@DonButeyn 

I would add Record ID tool to add row ID to each record. Then I would use a Select  Record tool and select  records from row 71 onwards in a separate data stream. Now I would join the two data stream like row id = row id+70 and do rest of the operations. Row ID 71 (in second data stream) and .row ID 1 (in first data stream) are in the same row now after the above join.

[BrandonB · Accepted solution]

What about a slightly different approach. You can use a Record ID followed by a formula tool that says [Record ID]-[Duration] which will result in the column  ID to be referenced, right? Then you could follow that up with a join tool that joins the data to itself with [Column to Reference] joined to [Record ID]. This would pull the corresponding row data that is needed. Then you could do whatever calculation needs done with the newly linked up data. 

I have attached a workflow as reference

 

[BrandonB]

@rituraj963 it looks like we have a similar idea!🙂

[DonButeyn]

Thank you! This worked great.