2 Replies Latest reply on Mar 5, 2010 6:57 PM by digs

    Calculate median of dynamic field

    digs

      Title

      Calculate median of dynamic field

      Post

      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.

       

      Thanks,

       

      Andrew

       

       

      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))]

      Else

          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]

       

        • 1. Re: Calculate median of dynamic field
          comment_1

          I don't think you have too many choices with your current structure. Perhaps you should consider breaking the values into individual records. Anyway, why don't you try something along the lines of:

           

          Go to Related Record [ Show only related records ; From table: "repmeasure" ]

          #

          If [ Get (ScriptParameter) = "Field A" ]

          Sort Records [ Restore ] //sort byField A

          ElseIf [ Get (ScriptParameter) = "Field B" ]

          Sort Records [ Restore ] //sort byField B

          # etc.

          End If

          #

          Set Variable [ $median ;

          Choose (

          Mod ( Get (FoundCount) ; 2 ) ;

          GetNthRecord ( GetField ( Get (ScriptParameter) ) ; Get (FoundCount) / 2 ) ;

          ...

          ) ]

          ...

           

          • 2. Re: Calculate median of dynamic field
            digs

            Thanks very much for that - I'll use your idea of an extended If statement. As you'll have noticed, I'm pretty new to FM, and so also hadn't found the "Go to Related Record" function - that tidies up the code quite a bit.

             

            Thanks for your help.