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.
Solved! Go to 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!
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