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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Formulas Across Fields

Meteor

In my workflow, I have combined two datasets into one file leaving me with two columns for every state. I have formatted the file to have the columns for each state next to each other. I am trying to write a formula to create a new field for each state and then insert it grouped with the other two columns for the state. Is there a way to write a formula to do the same thing after each two columns (ex. column 2 less 3, then column 4 less 5)? I have attached in Excel what I am trying to have happen in green font. I know I could write multiple formulas referencing each of the columns, but I am not familiar is there is a way to write a formula based on column patterns. 

Alteryx
Alteryx

@denvic29 

 

I'd approach this slightly differently. I'd transpose the data so that I have the state name in a single column and the two columns I'd like to use in the formula side-by side. Once calculated, I'd pivot the data back and append to the original data set. Example attached.formula-across-fields.png

 

 

Pulsar

Hi @denvic29 

Here is one solution. I've done a few transpose and crosstab, one to get the data in a format where we can summarize (by turning the return column to a negative number, and then adding together), and then one at the end for order purposes.

 

This should work even as you add in more states.

image.pngLet me know if this works.

 

Cheers!

Esther

Labels