Translating a Formula to a Multi-Field Formula

[TheGene]

Hi, I currently have a workflow that normalizes a data set, and then computes a % change from 2 data sets. I take the data, clean it up a little, and then generate a max, min, and average for each value. with these values, I generate a new column that is normalized.

workflow overview

the formula takes each of these values from the new columns and then outputs the new normalized value in the new field. from here, I then take each one of the values calculate a percentage change of the two data sets.

formula

calculated difference between 2 different data sets (left and right)

Percentage change Formula

My dilemma is that I have to do this exact process for about 40 different columns, and figured that there's probably a better way in Alteryx using using the multi-Field Formula to do this for all 40 at once instead of doing each one manually. I just cant seem to figure it out. i'd appreciate any help!

[DiganP]

@TheGene Would you be able to provide a sample dataset with the columns? 

From a high level, I would use the transform tool category (cross tab/transpose) and the mutli row formula tool to set up the logic. You want to think about your dataset in a row format rather than a columnar format. You should have a key/value pair and then apply the multi-row formula tool to apply the logic. 

[TheGene]

Hey @DiganP, I appreciate the reply. Attached, I have the Alteryx workflow shown above and also some of the sample data that could be plugged in, labeled accordingly in Excel.

I'm not sure exactly how transforming (transposing) the multi row formula would help, could you elaborate a little?

Cheers.

Maureen

[DiganP]

@TheGene Thanks for sharing the dataset. The columns don't seem to match up however. kW/ton seem to be missing.

However, I did put together a sample workflow with the logic that you are looking for. Hope it helps. 

[fmvizcaino]

Hi @TheGene ,

I have created an example simplifying your input data. The idea is to calculate everything automatically.

Let me know if this works for you.

Best,

Fernando Vizcaino

[MarqueeCrew · Accepted solution]

This challenge allows me to use a CReW Dynamic Formula.

I simplified the workflow to just calculate the delta.

Taking data into the formula, I joined Left and Right input on record position (fast!).  Then used the name of the NUMERIC fields to calculate the difference using an expression like:

"["+name+"]" +" - [Right_"+name + "]"

You could alternatively store all of the expressions in Excel for each formula that you want to calculate.  The required fields are:

NAME|TYPE|SIZE|Expression

When incoming data meets the dynamic formula, it will calculate all of the required fields.

Cheers,

Mark

[TheGene]

Thanks! all the responses were different ways to get to the same path, but this one was the most dynamic. Much appreciated!