9 Replies Latest reply on May 30, 2010 4:24 PM by choofer

    Calculate elapsed time



      Calculate elapsed time



      Very new to forums, and moderately new to databases.

      I have a problem trying to calculate days that patients have a specific device inserted into them as an outpatient. This is neceassry for producing like for like reports to compare with other hospitals.


      I have the fields "date commenced on program", "date off program", and a calculated field "program time"

      Program time gives me the number of days a patient has been on the specific outpatient program.


      Nor,al reporting times run from 1 january to 30 June, and then again from 1 July to 31 December.


      My problem.

      If a patient starts the outpatient program before 1 July and finishes before 31 December, they are not captured in my search criteria, FIND date commenced on program "01/07/2009...31/12/2009"


      This now gives me inaccurate data as there are a small number of patients that commence before 01/07/2009 but a portion of their program time falls into the reportable period.


      Be gentle with me 'cos i am only self taught and a slow learner!!

        • 1. Re: Calculate elapsed time

          If I understand correctly, you want to:

          (a) find all patients participating in the program during the report period - either fully or partially;

          (b) for the partially participating patients, calculate the portion of their particiating period that falls in the reported period.

          If so, I'd suggest you use two globalDate fields, gReportStart and gReportEnd to hold the reported range. Then you can calculate the participating portion as:

          Max ( Min ( EndDate ; gReportEnd ) - Max ( StartDate ; gReportStart ) ; 0 )

          To find the relevant patients:

          Enter Find Mode []
          Set Field [ StartDate ; "≤" & gReportEnd ]
          Set Field [ EndDate ;  "≥" & gReportStart ]
          Perform Find []

          • 2. Re: Calculate elapsed time


            Yea, you are right in what i am trying to do, well done.

            But, as i said, i am a slow learner and am not really sure whow to implement your reply, but i am going to give it a try.


            If it helps, am i able to send / post a clone of what i have?

            I do not expect anyone to add my fields for me, but as i am so slow i would appreciate it if you could have a look so that i don't waste a whole lot of time.



            • 3. Re: Calculate elapsed time

              Thanks Comment.

              It almost works.

              The patients that fall into the partial range do have their days on the program calculated, but they are all 1 day short.

              i.e. if a patient finishes on 1 July the calculated days = 0 when it should be 1, and a patient thatfinished the program on 19 August should have 50 days but is calc'ed at 49.


              Is there a simple fix?

              Can a script be written to catch all patients in the the one report?

              Or, am i forever going to have to perform 2 separate searches??

              Our hospital would like these reports monthly internally, but reported nationally 6 monthly.



              • 4. Re: Calculate elapsed time

                Ignore most of my last.

                It does work in one search, but the calc'ed number is still out by -1 on each of the subset of 4 patients that i have now found.

                The problem was that i didn't read your solution propoerly, i.e. all my fault, Soz!


                So, is there a simple fix?


                • 5. Re: Calculate elapsed time

                  If your original calculation was =

                  EndDate - StartDate + 1

                  i.e. including both StartDate and EndDate, then you should add 1 in the current calculation as well:

                  Max ( Min ( EndA ; EndB ) - Max ( StartA ; StartB ) + 1 ; 0 )

                  • 6. Re: Calculate elapsed time

                    To Comment.

                    Thanks for your help, it worked.


                    IOU 1 x


                    Now, is it possible to calculate mean and median on the "days in the program"?

                    I have found out how to get an average, but cannot find either mean or median.

                    • 7. Re: Calculate elapsed time

                      Using FM 10.0v1

                      • 8. Re: Calculate elapsed time

                        The average is the (arithmetic) mean.


                        Calculating the median of the found set is a bit more problematic, because records must be sorted by the value for this. If your report requires a different sort order, then you need a script to (a) sort by the value, (b) calculate the median and write the result into a global field, and (c) sort the records as required by the report.

                        Once records are sorted by the value, the median is =


                        Let ( [ 
                        n = Get (FoundCount) ;
                        mid = Div ( n ; 2 )
                        ] ;
                        Case ( 
                        Mod ( n ; 2 ) ; GetNthRecord ( Value ; mid + 1 ) ;
                        .5 * ( GetNthRecord ( Value ; mid ) + GetNthRecord ( Value ; mid + 1 ) ) 



                        • 9. Re: Calculate elapsed time


                          You are a legend!!

                          You should be on a payroll for your knowledge.


                          Thank you, eM