Jump to content
Sign in to follow this  
john9210

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 this post


Link to post
Share on other sites

Where did you get the file Difference.fmp12 ?

Share this post


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

Share this post


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


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.

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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

Sign in to follow this  

×
×
  • Create New...

Important Information

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