# Difference between two records with Self Join

This topic is 794 days old. Please don't post here. Open a new topic instead.

## 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

##### Share on other sites

This topic is 794 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account