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

# Average Calculations based on Person

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.

Jesse

• ###### 1. Re: Average Calculations based on Person

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.

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

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

• ###### 2. Re: Average Calculations based on Person

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

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