3 Replies Latest reply on Jan 6, 2012 12:32 AM by user12569

    Average Calculations based on Person

    user12569

      I am a bit embarrassed to ask this question, but I have been trying for two hours now to make the solution:

       

      Have a single table ith 20000 records. One field is Name. There are a few thousand unique names and the same name will show up four times or so within the dataset. I need to average a field called Amount based on the Person.

       

      So, Jesse is there 4 times with amounts of 20,10,30,20 Well, that average would be 80/4 =20, but I cannot for some reason get it to calculate the average based on any records in the dataset whose name is Jesse.

       

      Furthermore, I would like it to just see which name is in the active record and then get the average based on the name of the Active record. i.e. look for all instances of 'Jesse' in this case, pull the numbers for Jesse and do the avg. calc.

       

      I did get a portal on here, but I need it to average the numbers in the portal basically is another way of putting it.

       

      Thanks in advance for thoughts.

       

      Jesse

        • 1. Re: Average Calculations based on Person
          RubenVanDenBoogaard

          Hi Jesse,

           

          If you make a self joining relation to the name field (table::name > name average::name)

          you have all the records with the same name related to your record

           

          Then use a calc to get the avarage like:

           

          Sum(name average::amount) / count(name average::amount)

           

          Hope that helps,

           

          Best regards,

           

          Ruben van den Boogaard

          Infomatics Software

          ruben@infomatics.nl

           

           

          created by user12569 in General - View the full discussion

          I am a bit embarrassed to ask this question, but I have been trying for two hours now to make the solution:

           

           

          Have a single table ith 20000 records.  One field is Name.  There are a few thousand unique names and the same name will show up four times or so within the dataset.  I need to average a field called Amount based on the Person.

           

           

          So, Jesse is there 4 times with amounts of 20,10,30,20   Well, that average would be 80/4 =20, but I cannot for some reason get it to calculate the average based on any records in the dataset whose name is Jesse.

           

           

          Furthermore, I would like it to just see which name is in the active record and then get the average based on the name of the Active record.  i.e. look for all instances of 'Jesse' in this case, pull the numbers for Jesse and do the avg. calc.

           

           

          I did get a portal on here, but I need it to average the numbers in the portal basically is another way of putting it.

           

           

          Thanks in advance for thoughts.

           

           

          Jesse

           

          Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

          Start a new discussion in General by email or at FileMaker Technical Network

          Manage your email preferences.

           

          FileMaker Developer Conference 2012 • Miami, Florida • July 16-18 • www.filemaker.com/devcon

           

          • 2. Re: Average Calculations based on Person
            comment

            user12569 wrote:

             

            I did get a portal on here, but I need it to average the numbers in the portal

             

            Assuming your portal is based on:

             

            YourTable::Name = YourTable 2::Name

             

            define a calculation field =

             

            Average ( YourTable 2::Amount )
            

             

            and place it on your layout.

            • 3. Re: Average Calculations based on Person
              user12569

              I wasn't using the self-joined table.  I created it, but wasn't using the self-joined table as the field to sum and count.

               

              MUCH APPRECIATED>  Thanks


              Jesse