8 Replies Latest reply on Jul 12, 2016 6:38 AM by buccillima

# Average three most recent data entries from a portal

Hello, I'm very new to all this so any help would be greatly appreciated. I am a teacher building a database to do, among many things, keep track of my students' test scores. Sometimes, however, it's beneficial to average a student's 3 most recent test scores. I have created a student table related to an tests/scores table. I have created a student table layout with a portal that shows test scores. Is there any way that I can average the 3 most recent test scores that appear in the test scores portal? I hope that is enough information to go on. Thanks for any help!

• ###### 1. Re: Average three most recent data entries from a portal

If the relationship to scores from students is sorted, then this calc will get that info:

Let([

scores = List(scores::totalScore);

v1 = GetValue(scores;1);

v2 = GetValue(scores;2);

v3 = GetValue(scores;3)

];

( v1 + v2 + v3 ) / 3

)

• ###### 2. Re: Average three most recent data entries from a portal

Thanks so much Mike..it worked! And thanks for the quick response!

• ###### 3. Re: Average three most recent data entries from a portal

Mike's calc will work if the scores are sorted by descending order by date.  If sort in ascending order, you will need to modify the calc.

Let([

scores = List(scores::totalScore);

total_scores = ValueCount(scores)

v1 = GetValue(scores;total_scores-2);

v2 = GetValue(scores;total_scores-1);

v3 = GetValue(scores;total_scores)

];

( v1 + v2 + v3 ) / 3

)

• ###### 4. Re: Average three most recent data entries from a portal

Thanks rgordon...I do have my portal sorted by descending order, but I was wondering what would happen if I changed it to ascending order. Thanks for letting me know!

• ###### 5. Re: Average three most recent data entries from a portal

Well I used Mike's calculation and it worked when the portal has only 3 test scores in it, but when I add more test scores, the average value of the most recent 3 does not change. Any suggestions?

• ###### 6. Re: Average three most recent data entries from a portal

Use Roy Gordon’s calculation to adjust for grabbing the last three from the list, instead of the first three.

• ###### 7. Re: Average three most recent data entries from a portal

"I do have my portal sorted by descending order, but I was wondering what would happen if I changed it to ascending order" - I don't think changing that will have any effect on the calculation.  You need to sort it (or change the sort) in the relationship definition, I believe.

I suspect that's what's causing your next problem; if there is no relationship sort set then FM will take the sort as 'by creation timestamp', so unless you specify a different sort you will always be averaging the first 3 scores created.

• ###### 8. Re: Average three most recent data entries from a portal

Alangodfrey, I sorted the scores by the date of testing (descending order) in the relationship definition. After entering 6 or more tests in the portal it worked! Thank you very much for helping me to resolve that issue. Thanks again Mike for the calculation!