1 of 1 people found this helpful
If you number your batches consecutively, create a calculation field cPreviousBatchNumber = batchNumber - 1, add a new table occurrence Batch_Previous, and create a relationship where
Batch::cPreviousBatchNumber = Batch_Previous::batchNumber
Batch::clipName = Batch_Previous::clipName
Now when you create a calculation, do it from the context of Batch, and access fields from the matching records of the previous records by referring to Batch_Previous, like
duration - Batch_Previous::duration
Thanks Erlost, I'm certainly on the right track. So this almost works, except if the clip is longer, a subtraction calculation won't work.
For example clip001 in Batch 1 is 4812 and in Batch 2 it's 5844.
I'm thinking some sort of If function might be necessary here? To calculate if the difference between the previous batch and the new batch is higher or lower before then calculating it?
Well, if all you care is the absolute difference, use Abs ( yourDifferenceCalculation ).
It's still not working quite right unfortunately, I'm using the following figures:
ClipName - Batch A Duration - Batch B Duration - Abs Calc Result
Clip001 - 9452 - 7181 - 2271 (Should be -2271)
Clip002 - 4812 - 5844 - 3608 (Should be +1032)
Clip003 - 5885 - 7124 - 2328 (Should be +1239)