tonyt1234567
asked on
Analysing Dates When Some Fields Contain Null values
Hi,
I have a survey measuring how patients view their experience in a hospital.
I am trying to measure how long, on average, patients have to wait between being admitted and having surgery.
I calculate this by the following fields:
[Surgery Wait Time]=[Date of Surgery] - [Date of Admission]
[Average Surgery Wait Time] = Average(Surgery Wait Time)
The challenge is that not all patients have had surgery--which produces erratic results (such as -7132828)
I have tried to create a conditional formula, to take out those date having null values, as follows:
[Surgery Wait Time] = If([Date of Surgery="","",[Date of Surgery] - [Date of Admission]
When I try calculating [Average Surgery Wait Time], my report doesn't calculate an average at all--instead, it prints multiple copies of the report, each having different values in the field [Surgery Wait Time]--many of which simply don't make sense.
How can I calculate dates where there are null values??
I have a survey measuring how patients view their experience in a hospital.
I am trying to measure how long, on average, patients have to wait between being admitted and having surgery.
I calculate this by the following fields:
[Surgery Wait Time]=[Date of Surgery] - [Date of Admission]
[Average Surgery Wait Time] = Average(Surgery Wait Time)
The challenge is that not all patients have had surgery--which produces erratic results (such as -7132828)
I have tried to create a conditional formula, to take out those date having null values, as follows:
[Surgery Wait Time] = If([Date of Surgery="","",[Date of Surgery] - [Date of Admission]
When I try calculating [Average Surgery Wait Time], my report doesn't calculate an average at all--instead, it prints multiple copies of the report, each having different values in the field [Surgery Wait Time]--many of which simply don't make sense.
How can I calculate dates where there are null values??
ASKER
In the above formula, my problem is that Filemaker DOES make a calculation, even though date of surgery is null.
This is because there IS a Date of Admission
So, a patient who was admitted on: 1 April 2004
and had surgery on: 5 April 2004
Wait time = 4 days
--
HOWEVER, if:
Patient admitted on 1 April 2004
There was no surgery (i.e. date of surgery = "")
I get an erratic number.
Because one of the values (and not both) is null, Filemaker is making the calculation.
When I try to exclude null dates, Filemaker tries to produce one report per record (it seems), rather than summarising by month.
This is because there IS a Date of Admission
So, a patient who was admitted on: 1 April 2004
and had surgery on: 5 April 2004
Wait time = 4 days
--
HOWEVER, if:
Patient admitted on 1 April 2004
There was no surgery (i.e. date of surgery = "")
I get an erratic number.
Because one of the values (and not both) is null, Filemaker is making the calculation.
When I try to exclude null dates, Filemaker tries to produce one report per record (it seems), rather than summarising by month.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
KF, Thanks for your help (again!!)
I used the IsEmpty function, and this seemed to do the trick. I am able to calculate average values and count properly.
I completely agree on detailed information for surgical reports--however, these surveys are designed to give top-level information to measure overall patient care. To do this, I have measured the percentage of patients requiring surgical care, and the wait time of these patients (on average).
The report looks something like this:
----
Number of Patients Surveyed:
Number of Patients Requiring Surgery:
Average Wait Time for Surgery (Surgical Patients only):
Average Total Wait Time (All Patients):
----
Wait Time Surgery is calculated as follows:
Wait Time Surgery = IF (IsEmpty(Surgery Date),"",Surgery Date - Date of Admission)
Average Wait Time Surgery = Average (Wait Time Surgery)
I then created a new field containing 'x' as a marker for those records having surgery dates. I created a Count of values for this field, to reach a percentage of patients having surgical care.
I used the IsEmpty function, and this seemed to do the trick. I am able to calculate average values and count properly.
I completely agree on detailed information for surgical reports--however, these surveys are designed to give top-level information to measure overall patient care. To do this, I have measured the percentage of patients requiring surgical care, and the wait time of these patients (on average).
The report looks something like this:
----
Number of Patients Surveyed:
Number of Patients Requiring Surgery:
Average Wait Time for Surgery (Surgical Patients only):
Average Total Wait Time (All Patients):
----
Wait Time Surgery is calculated as follows:
Wait Time Surgery = IF (IsEmpty(Surgery Date),"",Surgery Date - Date of Admission)
Average Wait Time Surgery = Average (Wait Time Surgery)
I then created a new field containing 'x' as a marker for those records having surgery dates. I created a Count of values for this field, to reach a percentage of patients having surgical care.
otherwise, empty field are ignored for the average calculation.
what is the [Average Surgery Wait Time] field format? number or date/time? it should be number.