Multi Column Formula

[Kerry]

Hello,

I have several rows of data that represent different demographic information. Each metric is repeated one time to show values for old time period and new time period. I simply want to compute the change between the two for each metric. Here is a sample of the first few rows of data:

Total Panel12/28/14 - 12/26/15	Total Panel01/03/16 - 12/31/16	Income Under $2000012/28/14 - 12/26/15	Income Under $2000001/03/16 - 12/31/16	Income $20000 - $2999912/28/14 - 12/26/15	Income $20000 - $2999901/03/16 - 12/31/16
4	5	7	8	3	1

 So, I simply need to insert a column measuring change between the two time periods for each column header.

Thanks!

Kerry

[NicoleJohnson]

Try something like the attached! Uses Transpose tool to put the metrics all in one column, RegEx to determine which categories are connected (Total Panel, Income Under $200000, etc.), then uses Multi-Row to calculate the metric difference for each category. Cross-Tab tools puts the metric titles back on the top as headers, along with the new Metric Difference columns for each metric.

Hope that helps point you in the right direction! 

Cheers,

NJ

[patrick_digan · Accepted solution]

@Kerry I've created a generic macro that might work in your case. One feature of the macro is that it lets you reference columns by their position when writing a formula. So in this case, you can write a formula like: 

[_CurrentField_:Column+1]-[_CurrentField_]

It currently adds the new fields at the end, but you could manually fix the order with a select tool or automatically with the Crew Macros field sort.