Multi Field Tool - Cross Column Calculations

[aesham]

Hello

I have two input files, Period 40 data and Period 35 data.  I've joined them together, side by side.  Period 40 data shows average pricing by size.  Period 35 shows average pricing by size for a prior period (hence the column headings preceded with a "P").  I am using the Multi-Field Formula tool to dynamically generate the difference between the periods.

I want to use the [_CurrentField_] minus the [_CurrentField_] + 5 columns to generate the difference dynamically in the "Diff-Avg" fields.  Is there a way to index to the right from the  [_CurrentField_] ?

My workbook is attached.  Thanks for your help.

Andrew

[Inactive User]

I would recommend doing that as follows:

1. Perform Join as you are doing.

2. Transpose and group by key, having all of these Avg and P-Avg fields as rows instead.

3. RegEx parse the P- to a new field and label it as "Previous". Then use a formula tool and label the others as current.

4. Then take cross tab with the new previous/current field as the header and the value as the Value field, Grouping on the rest of the fields.

5. Finally, create a new field using formula called difference and do Current - Previous. The measure types will now be row based instead of column based, which allows for easier navigation here.

[aesham]

Hi Ryan,  thanks for the help.  I've been trying to work through your solution but I'm stuck on points 2 & 3.  Can you provide a little more direction?  Many thanks.

[jrgo · Accepted solution]

@aesham,

Attached is a workflow that I believe should help get you what you need. This is similar to what @Inactive User suggested, but instead, this transposes your two sources beforehand, joins them together, calculates the diff and crosstabs back which is then joined into the data set from your original join.

Basically, the bottom part I added does what you were trying to have the Multi-Field formula do, which is not designed to do this sort of operation.

Hope this helps!

Jimmy

[aesham]

Awesome!  Thank you Jimmy.