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
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!
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.