6 Replies Latest reply on Jul 24, 2012 6:55 PM by DarrenFrancis

# is it possible to assign a weight to calculations based on record numbers?

### Title

is it possible to assign a weight to calculations based on record numbers?

### Post

I have a table keeping track of performance.

Is it possible to have a summary field that does something like this:

Score = ((Average of last 10 records) * 0.5 + (Average of records 11 to 30) * 0.4 + (Average of records 31 to 40) * 0.1

Records after 40 are ignored as it would not reflect the current performance of the person.

The order of the records is based on a field that keeps track of the assignment date that this feedback is for.

Thanks in advanced for the help.

• ###### 1. Re: is it possible to assign a weight to calculations based on record numbers?

Make sure each record has a sequential number from 0 to N, say named RecID.  Assuming you want a running average if more than 40 records.

Score = (( Avgerage of (N-30 to N-39)*.5 + (Average of records (N-10 to N-29))*0.4 + (Aveage of records(N to N-9))*0.1

Where N= If ( RecID ≥ 40 ,Max (RecID),"")

Now create a relationships using Calcualted Global fields called, for example  gN_30 and gN_39 useing the Last record number created as N.

gN_30 = N - 30

gN_39 = N - 39

Now create a self relationship for the example:

RecID ≥ gN_30  AND

RecID ≤ gN_39

Now Average ( YourDesiredField )*0.5

Now "rinse and repeat" for each of the other ranges

This was a quick idea for concept,,,,

Jim...

• ###### 2. Re: is it possible to assign a weight to calculations based on record numbers?

Since your summary field is based on your found set and you've indicated that you will keep the records sorted, try these unstored calculation fields:

cLast10 : If ( Get ( RecordNumber ) > ( Get ( FoundCount ) - 10 ) ; ScoreField )

cLast11_30 : Let ( [ Fc = Get ( FoundCount ) ; N = Get ( RecordNumber ) ] ; If ( N > ( Fc -30 ) and N < ( Fc - 9 ) ; ScoreField ) )

cLast31_40 : Let ( [ Fc = Get ( FoundCount ) ; N = Get ( RecordNumber ) ] ; If ( N > ( Fc -40 ) and N < ( Fc - 29 ) ; ScoreField ) )

Define sAvg10, sAvg11_30, sAvg31_40 as the average of these three fields.

Then use this calculation:

sAvg10 * 0.5 + sAvg11_30 * 0.4 + sAvg31_40 * 0.1

• ###### 3. Re: is it possible to assign a weight to calculations based on record numbers?

Phil, very clever approach.  Darren's FoundCount must never be greater than 40 and to compare "Apples to Apples scores" between found sets, must be 40.  Otherwise his mathmatical weighted Average would be biased to a found set containing say only 15 to 20 records.

Add this to Phils if you need a compartive score...

If ( Get ( FoundCount ) ≠ 40 ; "Biased Weighted Average" )

Jim...

• ###### 4. Re: is it possible to assign a weight to calculations based on record numbers?

Hi Jim,

The found count can be any size of 40 or larger and this works. I don't see the need for that added calculation. The records that have a value in the calculation fields count backwards from whatever record is last in the found set. If you have 43 records in your found set, records 1, 2, 3 will not have a value in any of the three calcualtion fields and thus will be ignored in the calculated result.

• ###### 5. Re: is it possible to assign a weight to calculations based on record numbers?

After 2 cups of coffee, Phil, yours does look at the "running" weighted average, if the records are chronilogically sorted with the Nth record being the newest.  I am accustomed to using ≤ or ≥ for clarity versus N-1 and < or >.

But i was mainly referring to the Mathmatical definition of Weighted Average.

So...

If there are less than 40 in the example, it is not comparible between scores, even using your clever method.

Therefore...

If ( Get ( FoundCount ) < 40 ; "Biased Weighted Average" )

Jim...

PS: If this was a Semester grade, I could pass the class by being lazy and doing say 25 of 40 homework assignments.

• ###### 6. Re: is it possible to assign a weight to calculations based on record numbers?

Once again, thats for the quick response. I'm still very new to filemaker. Logic wise, it looks right to me. Now I just have to figure out how to implement it. Will let you guys know if all works out :D

~ Cheers!