1 Reply Latest reply on Jul 22, 2014 12:03 PM by philmodjunk

    Calculating day since first date for ID



      Calculating day since first date for ID


           We are currently using FMP13 in clinical research. We have 3 tables at the moment (Patients Info, Patient Drugs and Daily Scores). In Patient Drugs, we record daily drug doses whilst Daily scores are daily recordings of scores. A portal is used to enter the relevant data. There can be multiple drugs/scores every day and each time we get a new portal row, a new timestamp is created. We wish to create a field that will calculate the study day which is the GetDate(timestamp) - the first date recorded for this patient. The first day will be study day 1. 

           I can just create this field and manually enter the study day but was wondering if there is an automatic way to do this as an calculation. 


        • 1. Re: Calculating day since first date for ID

               If you only want elapsed days, date fields are simpler to use than timestamps which record both date AND time and store that as a value in seconds where dates store the value as a value in days.

               If you set up a field in the Patients Info field as a date field, it can auto-enter the creation date as your earliest date from which you want to compute elapsed days. You can also define a calculation field as Min ( RelatedTable::Date ) and select date as the result type to get a date in PatientInfo that is the earliest date in a related table such as Patient Drugs or Daily Scores.

               Either way, a calculation field defined in the child table can subtract a date in that table from the date in Patient Info to compute elapsed days:

               DailyScores::Date - PatientInfo::StartDate

               IF you use timestamps instead of dates, the difference will show the number of elapsed seconds and you'll need to either specify a TimeStamp result type or divide the difference by the number of seconds in a day (24 * 3600).