# Difference between two records with Self Join

## Recommended Posts

I’m using the technique described in Difference.fmp12 to find the difference between values on one record and on a previous record. The technique uses a self-join relationship and the Last function.
I use the following formulas:
Total = Amt1 +Amt2
Total2 = Last(Self_difference2::Total), where Self_difference2 is the self-join TO
Change = formula If(IsEmpty(Total2),””,Total -Total2)
The if statement suppresses the output on the first record since there is no previous record.
The technique seems to be dependent on the sort order. However, in one file I find the records must be sorted ascending and in another file the records must be sorted descending. No sort order is specified in the self-join relationships.
Can anyone explain this?

##### Share on other sites

Where did you get the file Difference.fmp12﻿﻿ ?

##### Share on other sites

It was included in a message on this site. Sorry, but I don't remember which one. Something to do with getting the difference between two records. I'm including a copy with this response.

Difference.fmp12

##### Share on other sites

Hello,

The calculation is base on the display so it depends of the order

You can do it without any link, it would run with any sort

Difference.fmp12

##### Share on other sites

I'm not sure what you mean about "link". In one file the records must be sorted ascending by date, and in the other descending by date. Strange.

##### Share on other sites

If you see at the file i posted you'll see that there is only one table in the graph.

In your file there is two tables in the graph that's what i mean with link.

In my solution, the sort isn't important, the calculation always work

##### Share on other sites

Great solution Tom! Very simple and easy to use. Many thanks for your help.

##### Share on other sites

No problem,

you could say it's resolved

Tom

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...