Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-Row Formula, go back X Rows

DonButeyn
5 - Atom

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

 

 

4 REPLIES 4
rituraj963
8 - Asteroid

@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
Alteryx
Alteryx

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

 

dynamic reference.png

BrandonB
Alteryx
Alteryx

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

DonButeyn
5 - Atom

Thank you! This worked great.

Labels