4 Replies Latest reply on Mar 23, 2016 3:41 AM by Vaughan

    ∆/Change/Difference/% - how to on FM

    happyez

      Hi everyone

       

      A bit of a pickle to be worked out (I've asked my science partner/searched on Google/FM Community) and to no avail so far.

       

      I am not a mathematician (IANAM), so be a bit easy on me.

       

      I have two numbers, and I want to find the % difference between them. Easy. x÷y.

      This is so I can show how has the biggest increase, sort it by the percentage (highest is 150%!)

      But what if one is 0? 0 => 2 would, in my mind, = 200%. But no, not possible because you are dealing with 0s

      So my partner came up with using ∆. You dont worry about what the number is, you just want to know the difference between x and y.

       

      If this is the best way to do it, then my next problem is finding a way to do it in a calculation.

      Just putting ∆ field1 / ∆ fieldd or ∆ = field1 / field2 doesnt seem to work.

      There must be a calculation that is worded in the Number calculations, but I cant seem to find it..

       

      Anyone have thoughts on this?

        • 1. Re: ∆/Change/Difference/% - how to on FM
          Vaughan

          happyez wrote:

           

          I have two numbers, and I want to find the % difference between them. Easy. x÷y.

           

          ∆ = y - x

          % = ( /x ) * 100


          Some people wouldn't *100 here, it depends on whether you want the result to be 100 or 1 for 100%.


          For the percentage calc it would be prudent to check for x=0 to avoid divide by zero error.


          delta_percent =

          If( Field1 ≠ 0 ; ( ( Field2 - Field1 ) / Field1 ) * 100 )


          If only the magnitude of the delta is needed (don't care about + or -) then use Abs()


          If( Field1 ≠ 0 ; ( Abs( Field2 - Field1 ) / Field1 ) * 100 )


          If you want to constrain the result to a certain number of decimal places, use Round(). To round to 2 decimal places:


          If( Field1 ≠ 0 ; Round( ( ( Field2 - Field1 ) / Field1 ) * 100 ; 2 ) )

          • 2. Re: ∆/Change/Difference/% - how to on FM

            There are lots of forums that have good math introductions for non-math folks.

             

            Below is one site I found with a five second Google search. 

             

            Percentage Difference

             

            FileMaker is quite capable of using just about any formula you could extract to a polynomial form, trigonometric, logarithmic, exponential, and lots more.

             

            HTH

             

            - m

            • 3. Re: ∆/Change/Difference/% - how to on FM
              happyez

              Thanks Vaughan

               

              It does indeed work. I tried it out, needed to deal with both Field1 and 2 being 0 (just equalling each other) and it was done. Thank you very much

               

              Morkus - that sounds a bit snarky "a five second Google search", well good for you. Sometimes I get things in five seconds, others not. You just need to have the keywords right, then it comes up. Chill dude

               

              Thanks all

              Eric

              • 4. Re: ∆/Change/Difference/% - how to on FM
                Vaughan

                happyez wrote:

                 

                ...needed to deal with both Field1 and 2 being 0 (just equalling each other) and it was done.

                 

                There is no need to deal with field1 and field 2 being equal, because ∆=0 is a valid result. Only field1 being zero is a problem, and that is trapped for in the calc.

                 

                Empty fields, on the other hand, are a different issue: what to do if data is missing. An empty Field1 is trapped for already: you need to decide what to do if Field2 is empty. As it stands the calc will return -100% (since this calc will treat an empty value as zero) which may or may not be appropriate for your circumstances. Returning null (no result) may or may not be better.