Auto-generate fields and then Auto formula based on field name

Question

Bear with me on this one

let’s say I have multiple sets of 12 months numerical data and the field names use the following conventions

Y1Jan, Y1Feb….. to Y1Dec

Y2Jan, Y2Feb……to Y2Dec

etc

I would like to auto-create fields with variances as data, so using a workflow another set of 12 months fields would be created and the data would be the variance eg

Y1JanvY2Jan field would be Y1Jan minus Y2Jan and so on for all 12 variances.

In reality I will have many sets of 12 months so I cant be creating each formula individually, it needs to be automated or using a macro. Also I expect I would need some way of specifying which variances I want to generate eg let’s say I just want Y1 months v Y4 months and then Y2 months v Y5 months.

hope that makes sense!

RogerS · Answer

Attach Auto generates the fields and does all combination of comparison.  You can set a range in the last filter for the range of years you want to compare.  for example, you can specify that you want to compare everything that is 2-4 years apart.

Auto generate fields with Auto formula.yxmd

JBLove · Answer

Hi @Carluccio555 -

This is a good use case for the Transpose Tool.

For example, let's say you have 24 months worth of data.  in the Transpose Tool group by your unique identifiers and other elements that you want to include.  Then for your Data Columns select the months in your data set (you will also want to check the "dynamic or unknowns column box" that way each time you run this the new months will automatically be selected).

Then you can use a Multi Row formula tool to calculate the difference between the current row and the 12th preceding row.  At this point, you will use a Cross Tab tool to restore the data set back a more horizontal structure.  I realize this is pretty high level as this is going to really be dependent on how much "prep" you have to do in your existing data.

Good luck.