Hurlz Posted March 26, 2020 Posted March 26, 2020 (edited) 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 Posted March 26, 2020 Posted March 26, 2020 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 Posted March 26, 2020 Author Posted March 26, 2020 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 Posted March 26, 2020 Posted March 26, 2020 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 Posted March 26, 2020 Author Posted March 26, 2020 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.fmp12
comment Posted March 26, 2020 Posted March 26, 2020 The equivalent of: Truncate (count/2;0) is: Div ( count ; 2 ) (for positive values of count, at any rate).
Recommended Posts
This topic is 1954 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now