community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Multi Column Formula

Meteoroid

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/15Total Panel01/03/16 - 12/31/16Income Under $2000012/28/14 - 12/26/15Income Under $2000001/03/16 - 12/31/16Income $20000 - $2999912/28/14 - 12/26/15Income $20000 - $2999901/03/16 - 12/31/16
457831

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

 

Thanks!

Kerry

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

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

Labels