Calculate median across record range

[Hurlz]

Hi, in the attached database, we collect a range of readings per date. I need to calculate the median for each element (eg temperature, pH etc) across a range of dates. There is one record per date.

The user will change the range of dates depending on what time period they want to view. The data doesn't need to be summarised by anything.

I am most of the way there, thanks to this great post: https://www.briandunning.com/cf/298. I have run the script in that link on a field doing a summary list. However, the list doesn't seem to sort as specified in the script, and the median value returned is not always accurate.

The lab results layout has been temporarily set up so I can see the summary field for each element below the median calculation field, with the actual data in the body. This way I could see what the sort was etc. I've just put in 5 records to test this, and to test what happens when there are odd and even records. sign in is admin/admin.

Grateful for any help!

H

 

 

Edited March 26, 2020 by Hurlz
...

[comment]

A simplified demo file showing only the problem would have been appreciated. AFAICT, the problem is that you're sorting the values incorrectly as text, instead of as numbers. 

 

[Hurlz]

thank you for that. I thought I had changed that for all calculations but had not. My apologies and sorry for not sending a simpler file too.

[comment]

Good.

BTW, I think you could make your calculation more elegant by using GetValue() instead of MiddleValues() and Div() instead of Truncate().  And I would also use ValueCount() to count values, instead of WordCount().

 

[Hurlz]

Thanks so much for that. All great except when I add the Div instead of Truncate as can be seen in the much simpler sample attached.  The median values with Truncate for these 5 records are correct. with Div they aren't - what am I doing wrong?

whitsunday_monitoring sample.fmp12Fetching info...

[comment]

The equivalent of:

Truncate (count/2;0)

is:

Div ( count ; 2 )

(for positive values of count, at any rate).

 

[Hurlz]

Thank you. I'll give that a go.