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.

SPetrie

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.

New Workflow4.yxmd