Jump to content

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?

Link to post
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

Link to post
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.

Link to post
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

Link to post
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.