6 Replies Latest reply on Feb 7, 2012 1:58 PM by raybaudi

    median value for a set of records single value.

    capnahab

      Title

      median value for a set of records single value.

      Post

      I need to find the median length of stay (los) of hospital patients. I have the data for los in a field for all patients. I can create a summary field for average but the agreed best marker is median.

      I am pretty new to filemaker but have found this 

      http://www.briandunning.com/cf/298

      I am not sure how to implement it as a custom function. I understand how to sort the records into ascending los.

      is this the way to go , or is there a simpler way.?

        • 1. Re: median value for a set of records single value.
          philmodjunk

          Here's a method that does not require a custom function, but does require scripting:

          Find your records.

          Sort your records

          Then this script:

          Go To record/Request/Page [ Div ( Get ( FoundCount ) ; 2 ) ; no dialog ]
          If [ Mode ( Get ( FoundCount ) ; 2 ) // odd number of records in found set ]
             Set Variable [$Median ; valu:e ( YourTable::LOS + GetNthRecord ( YourTableLOS ; Get ( RecordNumber ) + 1 ) / 2 ]
          Else
             Set Variable [$Median ; value: YourTable::LOS]
          End If

          This leaves the Median in a variable from which it can be copied into a field for future reference. If you make the field $$Global by adding the extra dollar sign and you have FileMaker 11, you can put the variable on your layout as merge text: <<$$Median>> and display the value immediately after running this script.

          • 2. Re: median value for a set of records single value.
            raybaudi

            @ Phil

            If there is an odd number of records, the median IS :

            GetNthRecord ( YourTable::LOS ; Get ( RecordNumber ) + 1 )

            Else

            ( YourTable::LOS + GetNthRecord ( YourTable::LOS ; Get ( RecordNumber ) + 1 ) / 2

            BTW: I did not understand why do you need to go to the Div ( Get ( FoundCount ) ; 2 ) record and NOT simply set the $$var with a calc like:

            Case(
            Get ( FoundCount ) ;
            Let([
            even = Div ( Get ( FoundCount ) ; 2 ) ;
            odd = Mod ( Get ( FoundCount ) ; 2 ) ;
            vEven = GetNthRecord ( YourTable::LOS ; even ) ;
            vNext = GetNthRecord ( YourTable::LOS ; even + 1 )
            ];
            Choose ( odd ; ( vEven + vNext ) / 2 ; vNext )
            )
            )

            • 3. Re: median value for a set of records single value.
              philmodjunk

              Yep, had them reversed and I thought of using GetNthRecord also a while after posting my first message...

              • 4. Re: median value for a set of records single value.
                raybaudi

                Ah, so the original script was something like:

                Go To record/Request/Page [ Div ( Get ( FoundCount ) ; 2 ) ; no dialog ]
                If [ Mod ( Get ( FoundCount ) ; 2 ) // odd number of records in found set ]
                Go to Record/Request/Page [ next ]
                Set Variable [ $$Median ; value: YourTable::LOS ]
                Else
                Set Variable [ $$Median ; value: ( YourTable::LOS + GetNthRecord ( YourTable::LOS ; Get ( RecordNumber ) + 1 ) ) / 2 ]
                End If

                But even so, you was going to a certain record without any permissions :)

                • 5. Re: median value for a set of records single value.
                  philmodjunk

                  I posted my original script. I just had it wrong. (the reversal was inide my own head ...Wink )

                  • 6. Re: median value for a set of records single value.
                    raybaudi

                    BTW: an unstored calculation ( no script and no custom function ) like this:

                    Case(
                    Get ( FoundCount ) and Get ( SortState ) = 1 ;
                    Let([
                    even = Div ( Get ( FoundCount ) ; 2 ) ;
                    odd = Mod ( Get ( FoundCount ) ; 2 ) ;
                    vEven = GetNthRecord ( YourTable::LOS ; even ) ;
                    vNext = GetNthRecord ( YourTable::LOS ; even + 1 )
                    ];
                    Choose ( odd ; ( vEven + vNext ) / 2 ; vNext )
                    )
                    )

                    could do the job, ensuring that sorting was by that field.