Dynamically reference columns in a formula using Multi-Field tool

[Maria_M12]

Hello Alteryx Community,

Is it possible to dynamically reference fields in the multi-field tool?

I have a dataset with 60 columns representing each month for a 5 year time span.  The dataset only contains values for the first 12 months, all other fields are set to 0.  I need to update columns 13 through 60 with a new value based on the value from the same month in the previous year.

In the screenshot below, column 13 would equal column 1 multiplied by some variable; column 14 would equal column 2 multiplied by the variable; etc

 

I know I can accomplish this by just writing 40+ formulas in the formula tool, but I was hoping to find a better way.

Thank you very much for your help!

[jdminton · Accepted solution]

Hi @Maria_M12 . I would suggest adding a record ID and using transpose to move the data into rows. Then use the multi-row formula to create a formula using something like Row-12 * 1.05 (or whatever change you want to make from the 1-12 columns. You will need to group the data by record ID in the multi-row tool. If you need help with this, please attach some data and/or your started workflow. I hope that helps!

[Maria_M12]

Hi @jdminton

That worked perfectly.  Thank you very much!

[jdminton]

Great! Please mark the answer as solved.