How to calculate mean time and std. dev. between hospitalizations

[Steven Cappiello]

I need a little help... a cancer research colleague who is new to FileMaker needs some advice.
Could recommend the best approach to tackle this?

 

I'm a bit stumped/stuck.

 

--

 

We need to calculate time between hospitalizations, let's call that H.

 

For instance: 

Patient “1” was first hospitalized from 1/10/13 till 3/1/13 then hospitalized from 2/5/14 till 3/1/14 

Patient “2” - from 3/2/13 to 3/8/13; 6/3/13 to 7/1/13; 9/1/14 to 9/15/14

Patient “3” - only 4/2/14 to 4/10/14

 

each patient will have different number hospitalization occurrences but in the range 1-10

 

We need to calculate mean time between hospitalizations and the standard deviation for patients with 2 or more hospitalizations.

 

Any help anyone could provide would be much appreciated and would be put to very very good use!

 

Thanks in advance.

 

---

[comment]

Does your colleague know how to populate an Interval field in the Hospitalizations table with the number of days since the previous hospitalization of the same patient (and nothing if no such previous hospitalization exists)?

 

What is the size of the study? How many patients, how many hospitalizations?

[Steven Cappiello]

not sure.. and ~200 patients... up to 10 hospitalizations.

[comment]

Assuming two tables: Patients and Hospitalizations, with the Hospitalizations table already having fields for:

• PatientID
• StartDate
• EndDate

 

1. Define the following additional fields in the Hospitalizations table:

• Interval (Number)
• sAvg (Summary, Average of Interval)
• sStDev (Summary, Standard Deviation of Interval)

 

Place these fields on the layout of Hospitalizations: Interval into the body part, the two summary fields into a grand summary part.

 

 

2. Define the following self-join relationship of the Hospitalizations table:

 

Hospitalizations::PatientID = Hospitalizations 2::PatientID
AND
Hospitalizations::StartDate > Hospitalizations 2::StartDate

 

  Sort the records from Hospitalizations 2 by StartDate, descending.

 

 

3. Back up your data before proceeding with this step. Go to the layout of Hospitalizations. Show all records. Click into the Interval field, and select: Records > Replace Field Contents… > Replace with calculated result =

Case ( Hospitalizations 2::EndDate ; StartDate - Hospitalizations 2::EndDate )

.

 

4. Check your data. To produce your report, find hospitalizations records that contain a value in the Interval field (or just show all records, since the two summary fields will ignore empty values). The two summary fields will give you the average and the standard deviation for the intervals in the found set.

 

 

Note that this is assuming that your data is static (i.e. no more records will be added to the ones already collected). Otherwise we would have to devise a way to populate the Interval in any newly added records (and potentially recalculate the existing ones).

[Steven Cappiello]

Thanks much Comment!  I'm pretty sure the data will be static at some point.