I'm trying to calculate P&L for our currency forwards. I need to subtract the unrealized gain from the current day from the previous day. I was able to accomplish this using a pivot function and then column subtraction pretty easily; however, the resulting columns are aggregate functions which depend on the values in the dates column (i.e. sum_unrealizedgainloss_12022017 and sum_unrealizedgainloss_12012017). When I replace the dataset with a different day, these columns do not populate based on the recipe.
I basically need to end up with one column that contains the difference between the current and previous unrealized gain for each ticker and account. Is there a way to do math across rows in my dataset? Doing the pivot and then subtracting the two resulting columns works, but as explained above, it's not repeatable. Ideally I'll be able to use the same recipe over and over again whenever I get new currency forwards data with different dates.
My source data looks something like this (very simplified):
Ticker | Account | UnrealizedGainLoss | Date
XYZ | 1 | 120.00 | 12/01/2017
XYZ | 1 | -5.50 | 12/02/2017
ABC | 1 | 15 | 12/01/2017
ABC | 1 | 0 | 12/02/2017
etc.
