
1. Re: Difference from previous row based on Condition
Chris Chalmers Nov 17, 2016 11:21 PM (in response to Naveen Vanaparthi)Hey Naveen,
I'm assuming you need this calculation to work even if there are multiple rows next to each other with null signature dates. With that assumption, I think the following table calc is what you're looking for. Also see the attached workbook.
With these table calc settings:
I added an extra row to your data to test multiple null rows next to each other, and here are the results:
See attached workbook.
Explanation
The calculation looks deceptively simple, but the logic behind it is a bit technical. Table calcs cannot keep stepping backwards in the partition conditionally. E.g. you can't say "for this row in the partition, use the first row before it for which some condition is true". Instead, they must know how many steps they're going backwards ahead of time. In this case, for each row of each partition we've written the table calc such that it examines the current row and the row immediately before it.
The calculation works by using a known property of the previous row (an invariant) to calculate the current row. By assuming that our calculation works as intended, we can assert the following invariant for the previous row:
[Expected Value Calc] = [Amount]  [Amount (prior nonnull signature date)]
We can then rearrange that invariant to determine that, for the previous row:
[Amount (prior nonnull signature date)] = [Amount]  [Expected Value Calc]
So if the previous row is null, We can evaluate [Amount]  [Expected Value Calc] for that row to figure out what the [Amount] is for the last row whose signature date is not null. That's what this part of the calculation is doing:
LOOKUP(ATTR([Amount]), 1)  PREVIOUS_VALUE(0)
And the rest of the calculation is built around that.
Hope that helps!
Chris Chalmers

diffnonnull.twbx 20.7 KB
