AnsweredAssumed Answered

Calculate median of dynamic field

Question asked by digs on Mar 5, 2010
Latest reply on Mar 5, 2010 by digs


Calculate median of dynamic field


I'm trying to calculate medians of about a dozen fields, and while I've been able to create a script to calculate the median for each, I'd like just one script to which I can pass the field for which I want to calculate the median as a parameter. However, calculating the median involves sorting records, and you can't (FM Pro 10) specify the field you want to sort at run time; I need a "Sort Field by Name" function. Below are a few more details, and my current median script; if anyone has any ideas of how to achieve this I'd be most grateful. Otherwise, can we please, please have a median function in FM?! Buying FM Pro 10 used up all my budget, so I can't afford to upgrade to advanced.







Table 1 (summary table containing medians): repcallsrecorded

      Fields: CallID, Median f5, Median f95, Median t5, Median t95, etc


Table 2 (table containing list of measures for each CallID): repmeasures

     Fields: CallID, f5, f95, t5, t95, etc


This is my current script to calculate median for a particular field (Freq 5%;f5). It works OK, but rather than have a separate script for each field, I'd like to have a general median script, and pass the field at runtime as a parameter. However, I can't get the Sort Records or GetNthRecord functions to use the parameter - they seem to require a specific field name:


Set Variable[$CallID,Value:repcallsrecorded::CallID]

Go to Layout["repmeasure"(repmeasure)]

Enter Find Mode[]

Set Field[repmeasure::CallID; $CallID]

Perform Find[]

Go to Field[Select/perform; repmeasure::Freq 5%]

Sort Records[No dialog]

If [Mod( Get(FoundCount);2)]

    Set Variable[$median; Value:Let(field=repmeasure::Freq 5%;GetNthRecord(field; Int(Get(FoundCount)/2)+1))]


    Set Variable[$median; Value:Let(field=repmeasure::Freq 5%;(GetNthRecord(field; Get(FoundCount)/2) + GetNthRecord(field; Get(FoundCount)/2+1))/2)]

End If

Go to Layout["repcallsrecorded" (repcallsrecorded)]

Set Field[repcallsrecorded::Median f5; $median]