Comparing Column by Row Values

[jalizmaldonado]

Hi, at the moment, I'm trying to understand how to compare two values in two columns by row. Essentially, I'm comparing previous data to current data and identifying if there's a difference. The difference will then feed into a variance report and downstream from that. I know I can use the formula tool to complete a conditional statement identifying whether there's a change and if so how much but I wasn't able to duplicate my efforts with the multi-field formula. Attached is the data I'm comparing along with the output in the second tab. 

[echuong1 · Accepted solution]

I believe this is what you're looking for.

The goal is to actually get your values in a Name-Value format, with the previous and current values in different columns. That way you can dynamically do your comparison/subtraction. In this case, I separated the data, and used a union and transpose/cross-tab to do this. Once I had that, I used a formula to find the difference for the numbers, and a string comparison for F2. I used a cross-tab to pivot the data back.

Hope this helps!

[hroderick]

use formula to add a column with calculated previous date based on current date

yse a join tool with same input on both sides, joining on current date = previous date

on the joined rows, calculate variance in a formula tool