Calculate difference between records in the same table

[FMquestions]

Hi,

I am trying to calculate the difference between .

I have a Person table, Object table, and Measurement table. There could be multiple types of measurement and multiple times of measurement. For instance, John has a fish. He measures the length of the fish and the weight of the fist. I am trying to calculate the change in each type of measurement.

Currently, my method is to have fields in the Measurement table to calculate the difference using the Let and Case function. For example,

lengthchange_c =

Let (

[difference=

Case(

Get (RecordNumber)>1;legnth- GetNthRecord (length; Get (RecordNumber)-1)

);

lastvalue= GetNthRecord (WHO; Get (RecordNumber))];

difference/lastvalue)

I have a summary report that displays all the measurements for a particular person and sorted by the objects. The problem with the method I have is that for object 2, the field will calculate the difference between the first measurement of object 2 and the last measurement of object 3. This problem arise because my current method completely depends on the previous record.

Does anyone have a suggestion on how I can do the calculation differently so that it will only compute the difference between subsequent measurements but recognize when the measurement is for a new object?

Thank you in advance.

[comment]

Are measurements entered in chronological order?

[FMquestions]

They should be. Each measurement will have a measurement date and time that is created automatically as the measurement is made.

[comment]

If you want the calculation to ignore the current found set and sort order, you need to get the previous value via a self-join relationship such as:

Measurements::ObjectID = Measurements 2::ObjectID

AND

Measurements::Type = Measurements 2::Type

AND

Measurements::Timestamp > Measurements 2::Timestamp

If you sort the records on the Measurements 2 side by Timestamp, descending, then Measurements 2::Value will return the previous measurement of the same object and type.

If records are entered chronologically, you can lookup the previous value into a local field - this can make large reports significantly faster.

[FMquestions]

Thank you for those suggestions. I don't think I want the calculation to ignore the found set and sort order. The report is summarizing the measurements for all the objects and separating the findings for each object. I need to calculate the difference between the measurements for each object. The method I posted earlier works if the person only has 1 object. Perhaps I should have the script create value list of the measurement for each object and then apply my posted method?

[comment]

I don't think I want the calculation to ignore the found set and sort order.

In such case you must use GetNthRecord() to get the data from the previous record. If your records are sorted by person and by object, make the calculation return a result only if the current person and object are the same as the previous record's.

[FMquestions]

Thank you for that suggestion. I modified the calculation as followed:

Let ([

M1 = Case(Get (RecordNumber)>1;length);

T1 = Case(Get (RecordNumber)>1;Object;

M2 = GetNthRecord (length; Get (RecordNumber)-1);

T2 = GetNthRecord (Object; Get (RecordNumber)-1);

difference = M1-M2];

If ( M1=M2; difference/M2)

)

However, the calculation is not returning any values. Do you have any suggestions?

Thank you.

[comment]

Try something like =

Let (

N = Get (RecordNumber)

;

Case (

PersonID = GetNthRecord ( PersonID ; N - 1 )

and

ObjectID = GetNthRecord ( ObjectID ; N - 1 )

;

Let ( prev = GetNthRecord ( Length ; N - 1 ) ; ( Length - prev ) / prev )

)

)

[FMquestions]

For some reason, that calculation only works for the 2nd measurement value for the first object. For all the subsequent measurement and objects, the calculation is not returning any values.

[comment]

Does this work for you?

PrevValue.zip

---

P.S. Please update your profile to reflect your version and platform.

[FMquestions]

Thank you. The calculation correctly return the previous measurement value for each object but does not calculate the difference between the value. Is this because only one calculation is allowed?

I updated my profile to reflect my FM version (11) and platform (WindowsXP). Thank you.

[comment]

The calculation correctly return the previous measurement value for each object but does not calculate the difference between the value. Is this because only one calculation is allowed?

No, it is because this is a demo, and I wanted to show clearly that the previous value is being fetched. If you look at the actual formula, you'll see where I made the modification to the one above.

[FMquestions]

Ahh I see. Thank you.