5 Replies Latest reply on Aug 23, 2014 6:30 AM by erolst

# Sorting an Array to Determine Median for a set of values

We use 6 domains for evaluating our trainees. Each domain has 2-7 categories. As the requirements evolve from accrediting agencies it is reasonable to expect additional categories in these domains. I have to calculate the average, median and skew for each domain. Calculating median, which is possible with FM, requires creating an array of numbers in one single field of every record, sorted in ascending manner. Sorting is not possible in a single field.

There are calculations available at other websites but these didn’t work well; inexperience, perhaps. So I created an array of sorted numbers as follows:

I created a new FM Table and a Layout with Name (ID) of the trainee and a generic number field. Using the import function, I imported these into as many records as there are categories for one single trainee, yielding 4 records of individual categories in a domain for a trainee, say in Professionalism. Then I sort the number field in ascending manner and reimport these values into a single field using a comma and space as separators. So the values are sorted in an ascending manner. This works well for one record at a time but I could not make it work through a Loop function. Also, I need to write as many sets of steps as there are categories to reimport the sorted values. If the number if categories increases then I have to have as many steps at that time.

Example and Question (Please see the Table below using 7 categories in Intrpersonal skills):

I would be grateful for your help.

Kesavan

• ###### 1. Re: Sorting an Array to Determine Median for a set of values

Kesavan wrote:

Calculating median, which is possible with FM, requires creating an array of numbers in one single field of every record, sorted in ascending manner. Sorting is not possible in a single field.

Actually, calculating the median just requires a sorted list of numbers; how you get this list and where you store it (or if you have to store it at all) are implementation details. The better normalized your data structure, the easier the solution

So I don't know your exact table structure, but you probably should have each numeric response in a record that is related to a Trainee (or even better, a TraineeAssessment) record and “knows” its category; then this can be done quite easily by using eSQL.

See the attached sample file (where obviously a question should come from a Questions table, where each question belongs to a category; but that's beside the point here …)

• ###### 2. Re: Sorting an Array to Determine Median for a set of values

Thank you. Each numeric response related to a trainee is in one record. A trainee assessment record, generated monthly, contains all 6 domains, with a total of 23 (sub)categories. My aim is to get a Median, Mean, Skew and Standard Deviation for each domain every month, regardless of how many categories are there. Thank you for the file. I shall try it. Kesavan

• ###### 3. Re: Sorting an Array to Determine Median for a set of values

Thanks.  On looking this up, I find that this solution is avaialble only since FileMaker 12. Unfortunately, my file ws created in FM11. I was using FM11. Perhaps I might switch all my files to FM12 since it offers some new solutions. Thanks agaian.

• ###### 4. Re: Sorting an Array to Determine Median for a set of values

It should work in FM 11, with a plugin, e.g.

http://myfmbutler.com/index.lasso?p=425

greg

> my file ws created in FM11

• ###### 5. Re: Sorting an Array to Determine Median for a set of values

gdurniak wrote:

It should work in FM 11, with a plugin, e.g.

http://myfmbutler.com/index.lasso?p=425

I was just about to recommend the BaseElements plugin, which sports a BE_FileMakerSQL() function.

On the other hand, upgrading to FM 13 isn't a bad idea, either .