Formula Tool dealing with Dynamic columns

[cjaneczko]

I have a formula tool that adds up the data in 3 columns, but each month the names on those 3 columns will change. The columns are the last 3 months of data, so for example it would be Aug/July/June. When September closes those columns will now be Sept/Aug/July. I had the formula tool adding Aug/July/June but now June doesnt exist as its rolled forward a month. How can I get the formula tool to add those three columns up each month dynamically.

As part of the Dynamic Column renaming I do have it adding the word Miss to the beginning of each of those three months. Is there a way to key in on the word "Miss" to add the columns up?

[ed_hayter · Accepted solution]

I went with a field info tool to get the names in order of appearance then use record ID to rename the months to their column position then do the calc based on the column position rather than name.

[FinnCharlton · Accepted solution]

Hi @cjaneczko , you can do this by parsing the month names from the field titles, then labelling the latest 3 month columns with numbers. The formula tool is then always working with the same field names:

[cjaneczko]

Thanks @FinnCharlton and @ed_hayter, this works. Is there a way to make use of a multi field formula to sum all fields that start with 'Miss_'? I can incorporate the above solutions, but ill have to rename the columns after the 1,2,3 etc. back to what they were and I was hoping to not have to do that.

[FinnCharlton]

@cjaneczko You can't do that using the multi-field formula tool, but you can using transpose, filter and summarise tools. Have a look at the attached workflow.