Dynamic formula based on field names

[jeriley1]

I've seen similar topics posted but haven't found an answer to my specific question. I have a data set that includes a set of variables as columns and each variable has a corresponding column that's an average of that variable by geography (example dataset attached).

What I want to do is create a new column for each variable which divides the variable by the corresponding variable average. My input will be dynamic in terms of the number of variables, so I can't set this up as a one time formula tool. but the naming convention will be consistent and thus each variable will have only one field in which the right 3 characters of the field name match, as there will always be less than 1,000 variables.

So I was thinking a possible approach would be a formula or loop that matches fields based on the right 3 characters of the field name and divides the first field by the second. But I can't figure out how to actual do this. 

All advice/suggestions will be appreciated!

Justin

[neilgallen]

@jeriley1 I'd recommend investigating pivoting your data based on geography and period and then doing the math on the resulting dataset. I'm not completely clear on what your desired output looks like so it's hard to say how to exactly go about it, but the best way to handle multiple fields that will be dynamic in nature is to pivot the data so that your column headers now become a single new field, with the values in a separate field. Then using a summarize tool you can do the math and transpose the dataset back to the desired format.

[Thableaus]

Hi @jeriley1 

Do the Average Fields all have "Avg" on the prefix?

Cheers,

[adamorse · Accepted solution]

 I attached a workflow that might give you some ideas -- basically, you transpose the data to get the field names in a column, calculate a column that just has the variable number, and associate the raw and average data to that column. Then you can do any math you want between the fields before crosstabbing to get the data in your desired shape. I'm not sure I understood 100% what you were after but this might work as a starting point.

[Thableaus · Accepted solution]

Hi @jeriley1 

Here's a different approach on @adamorse solution, which is also a good one.

- Transpose fields

- Create Right 3 characters rule

- Separates Avg from regular values

- Join them to put side by side

- Use Formula tool do divide one by another

- Cross-Tab raw values, avg values and division_avg values to columns

- Join Multiple everything.

You'll have everything under columns and this works for any dynamic dataset.

Workflow appended.

Cheers,

[jeriley1]

---

@adamorse wrote:

 I attached a workflow that might give you some ideas -- basically, you transpose the data to get the field names in a column, calculate a column that just has the variable number, and associate the raw and average data to that column. Then you can do any math you want between the fields before crosstabbing to get the data in your desired shape. I'm not sure I understood 100% what you were after but this might work as a starting point.

---

This is exactly it! The final output of your example is the new data set of "normalized"  variables. I don't want to admit how long I was trying to get to this solution, thanks for the rapid answer!

[jeriley1]

Thank you @Thableaus. Your solution is more similar to the workflow I was attempting, the difference being yours produces the desired output!