Having trouble calculating Median

[rkass068]

Hello, I am currently using FMP 12 and I have been searching everywhere for how to calculate the median number of a field for a set of records. 

 

I really would like the median to be a summary field where I can sort some records and pull a median. Mean is not good for my use.

 

Here is what I am trying to do:

 

1. I have a database that calculates the wait time of each patient for an appointment and I have to pull out wait times to give to the hospital.

2.A mean is not good as there can be outlyers depending on their diagnosis

3. Important fields are Patients:Wait times, Patients:doctor_assigned

4. I want to run reports for each doctor (i am currently using a find) and I want the median wait times to update

 

Really I need an unstored median calculation but I do not know how to calculate the median wait times for the records found. 

 

Thank for the help

[Lee Smith]

This need has come up in the past, do a search of Google for

 

site: fmforums.com find the median

 

and see if this provides any help.

[mr_vodka]

http://fmforums.com/forum/topic/51892-calculating-median/

[Lee Smith]

That was one of the threads that came up in the find I provided.

[rkass068]

That was one of the threads that came up in the find I provided.

Thanks for the threads. I am falling into problems, however. What I am trying to do is to sort all of the patients by their doctor and I want to see their median wait times. The problem is I have to sort them from least to greatest wait times to get the median with the script given in the threads. This removes the grouping by doctor and I can no longer chart it. It seems to do this I would need a summary field as I tried it with the average summary calculation. If I cannot do this then I will probably just use excel. Thanks

[eos]

If I cannot do this then I will probably just use excel

 

We wouldn't want that to happen! 

 

You could use a self-join (or a lookup via ExecuteSQL) to find patients with the same doctorID (plus other criteria, if necessary), build a list of their waiting times and feed that list to the median calculation.

 

Depending on your exact data structure, other techniques may be suited as well. Why don't you post a sanitized version of your database (sans patient data) so we can have a look at it?

[Lee Smith]

Did you down load the attachments?

[rkass068]

We wouldn't want that to happen! 

 

You could use a self-join (or a lookup via ExecuteSQL) to find patients with the same doctorID (plus other criteria, if necessary), build a list of their waiting times and feed that list to the median calculation.

 

Depending on your exact data structure, other techniques may be suited as well. Why don't you post a sanitized version of your database (sans patient data) so we can have a look at it?

 

Sure, So I want to be able to click on Reports:Wait times then Hematology and I want a report with a chart showing the median wait times for every doctor in a chart form (bar graph maybe). Right now I do not have a chart made, but I know it will work with the summary field (Average Wait times) since its an average. I just want to change that to a median but FMpro 12 doesnt have median for some reason. Oh and how do i attach a file? hahah

Did you down load the attachments?

yes I did, but it doesn't seem to do what I want. I can get the median of all of the records but I wanna be able to run a report with a chart based on each doctor. 

[eos]

Oh and how do i attach a file?

 

There's a link to an instructions page which Lee could give you, but I don't have it handy, so here's the short version:

 

It's best practice to post a zipped version of the file. On the Mac, navigate to your database file in the Finder, right-click it and select “Compress [filename]”. Then start a new reply in this thread, and choose More Reply Options; on the next screen, under “Attach Files” click on the Choose File button, select your zip archive in the dialog, attach it, then post the message. 

[rkass068]

Here is the file. Thanks! 

Hematology referal database Clone.fmp12.zip

[comment]

 The problem is I have to sort them from least to greatest wait times to get the median with the script given in the threads. This removes the grouping by doctor

 

You must sort by the parent doctor first, then by the value. That will preserve the grouping AND provide a sorted list for each group. Then you only need to pick the correct record/s within that group. See the attached for one possible method to do that.

MedianByParent.fp7.zip

[Raybaudi]

 

 

Posted Today, 05:55 PM

 

[rkass068]

You must sort by the parent doctor first, then by the value. That will preserve the grouping AND provide a sorted list for each group. Then you only need to pick the correct record/s within that group. See the attached for one possible method to do that.

Okay, thanks I see how you did this. Will the median calculation auto update if I do a search. For example, If I want a monthly report for wait times, can I report median wait times by doctor in the last month? thanks!

[comment]

Yes - the calculation reflects the found set in the child table. It must be sorted, though.

[bcooney]

So happy to see you stopped by.  

[rkass068]

Yes - the calculation reflects the found set in the child table. It must be sorted, though.

The calculation and the report are working great! Thank you. However, it seems that I cannot make a chart in the report. Some of the grouped data medians are correct and some are not on the chart. Is there a way to chart this better? thanks

 

I figured it out. thanks!

Edited August 30, 2013 by rkass068

[rkass068]

Sorry for bumping this thread but I have another question regarding the median.

 

Is there a way to calculate the median like how its shown above but it will calculate based on the sort. With this calculation the median is only calculated depending on how its grouped when defining the variable in the calculation (see file above).

 

So for example right now if i change the sort order that doesn't depend on the grouping, it does not show the median deepening on my find and sort.

 

Do I have to make a calculation field for every sort that I will use. Currently I am grouping the patients by which doctor they have to see their median wait times. However, sometimes I want to change the sort depending on the diagnosis so it shows the median for each diagnosis

 

thanks for your help!

[comment]

Do I have to make a calculation field for every sort that I will use

 

You could have the script declare a variable containing the name of the field you sort by. Then use this in the calculation field as:

groupSize = GetSummary ( sCount ; GetField ( $sortField ) ) ;

 instead of:

groupSize = GetSummary ( sCount ; ParentID ) ;