Solved

Analysing Dates When Some Fields Contain Null values

Posted on 2004-04-23
4
310 Views
Last Modified: 2010-04-27
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??

0
Comment
Question by:tonyt1234567
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 28

Expert Comment

by:lesouef
ID: 10909758
yr surgery wait time is in days, therefore, the wait time will often be 0 if day admission=day surgery
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.
0
 

Author Comment

by:tonyt1234567
ID: 10910086
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.

















0
 
LVL 2

Accepted Solution

by:
KungFoolio earned 500 total points
ID: 10922620
Tony,
Problem #1:
Surgery Wait Time = If(IsEmpty(Date of Surgery),"",Date of Surgery-Date of Admission)

(If Date of Surgery is empty, return nothing, otherwise return Date of Surgery - Date of Admission)

Problem #2: "When I try to exclude null dates, Filemaker tries to produce one report per record (it seems), rather than summarising by month."

You would want to do this anyway before running surgical reports, because you only want to show the surgery wait time for thos people that had surgery - and therefore you want all of your denominators to be correct - especially if you are using summary fields..

Script the report to perform the find Date of Surgery "=", then find omitted, then remember to Sort the records by whatever your sub-summary report break field is.
0
 

Author Comment

by:tonyt1234567
ID: 10932245
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.



0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question