Best practice to calculate difference to the first row (constant) for multi variables

[goutdelete]

Hi everyone,

I have modeling where I need to keep the first row record as Period 0 and calculate difference between all consequent rows to the first row.  Basically something like this:

The problem is that in the real file I have more than 5 variables (in this sample I only have 3) and more than 5 scenarios (here I only have S1/ S2).  I wonder if there's a good way to go at it so I don't need to do formula to every item one by one? (something like what I did in the screenshot below)

I've also tried to do different ways with multi-rows and multi-fields, it would cut down the number of the formula items but doesn't seem to be able to something like: For each scenario, minus the first row (Period 0) for each of the corresponding variable.  

i.e Is there a way to apply some variable in Alteryx and accomplish this task in just 1 multi-field and 1 multi-row tool? Or do I require macro for it..

Thanks!

* Attached the sample workflow.

[jdminton]

I would use a transpose and crosstab to lay the data out better and multifield formula tool to calculate. As long as the calculation is the same, you should have no issues. See attached.

 

[AndrewDMerrill · Accepted solution]

First off: Thank you for including a workflow! That helps tremendously :D

You are on the right track, but just need a little bit more to get to the answer you're looking for! The key combo is the Transpose Tool and Cross Tab, that way you only need to work with one column instead of an arbitrary number. I modified your original workflow (You need to change the Sample Tool configuration, and swap the Append for a Join Tool, etc.):

Hope this helps! Happy Solving!!!

[goutdelete]

Amazing!  Thanks a million @AndrewDMerrill!! 

Indeed when it comes to transpose we don't use it all that much and sometimes it's not as intuitive to go that route right off the top of my head.