4 Replies Latest reply on May 25, 2017 10:09 AM by tetrasync

Calculate the difference between two records based on information in the same field

I'm trying to build a calculation field that will calculate the difference between a new value and an old value the last time it was entered.

For example I have clips that vary in duration but have been categorised into batches so for example batch 1 may look like this.

Batch - Clipname - Duration

1 - Clip001 - 52

1 - Clip002 - 76

1 - Clip003 - 19

1 - Clip004 - 12

The next time  around I'd import a similar list for Batch 2, note its the same clips just some have changed duration.

Batch - Clipname - Duration

2 - Clip001 - 37

2 - Clip002 - 94

2 - Clip003 - 19

2 - Clip004 - 22

How do I then go about creating a calculation field that will look for the previous import batch and compare the durations? So the field would show as + or -. So the calculation for Clip001 in Batch 2 would show as -15.

Bit of a fiddly one but I'm sure its possible without scripting

Many Thanks!

• 1. Re: Calculate the difference between two records based on information in the same field

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

cDifferenceToPrevious =

duration - Batch_Previous::duration

1 of 1 people found this helpful
• 2. Re: Calculate the difference between two records based on information in the same field

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?

• 3. Re: Calculate the difference between two records based on information in the same field

Well, if all you care is the absolute difference, use Abs ( yourDifferenceCalculation ).

• 4. Re: Calculate the difference between two records based on information in the same field

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)