Reference an offset column using _CurrentField_

[danielbryden]

Hi, I've done some searching but haven't found the answer to this yet. I'm trying to calculate claims development using claims triangles. I have 20 columns called 1, 2, 3, ..., 20. I need to be able to calculate new columns which are [2] / [1], [3] / [2], [4] / [2] etc.

I was hoping I could do something like [_CurrentField_] / [_CurrentField_-1] where -1 represents the field to the left of the current field.

Is something like this possible? The number of fields will increase as more years appear (the fields come from a pivot of the data.

Thank you in advance for any help. I'm just trying to avoid creating 20 calculations.

[Luke_C]

Hi @danielbryden 

Some sample data/expected results might be helpful, but my initial reaction is to transpose the data and use a multi-row formula tool. That definitely will give you the flexibility to go -1 row. Then you can always cross-tab back into columns after the math is done.

[SPetrie · Accepted solution]

I dont believe you are going to find that functionality with the multi-field tool.

Im a bit thrown off by the [4] / [2] part of your example since it was two columns over instead of 1. If that is a mistake and its always one column over, you may have better luck with the muli-row tool instead. (even if thats not a mistake, its still possible but a bit more complicated for the formulas.)

Dummying up some data, I came up with this example flow that may do what you are wanting.

I add a record Id and then transpose using the record ID and any main data that is not included in the fields we want to do division on.

In a parallel branch, I use a multi-row grouped by the record ID to divide the current row by the one previous to it. Use another multi row to create a new column header for each pair (that part is up to you on how you want the new fields named, I just used current plus one above)

I used a select to drop out the original column names and values and changed the names of the new fields and unioned them back to the main branch.

Cross tab them to bring the back to the original orientation.

After that, its a matter or a select to put them in the order you prefer.

Any new columns will automatically get added to the mix.

[SPetrie]

Forgot one other method. The Dynamic Formula tool in the CReW macros can also accomplish this task. Still uses multi-row to create the formulas, but the sorting and naming of the output columns is nicer.

[danielbryden]

This is great, thank you very much! Yes - the [4] / [2] was a mistake, so thank you for assuming it was. The more I use Alteryx the more I find it to be incredibly good, and I know I'm only scratching the surface. And now I know how great the community is. Thank you again.