Trying to Calculate first lowest value...

[Pixbo]

I'm in need of inspiration.

I am creating a database that will follow patients after treatment for a condition. One feature of the database will track the results of a blood test over time. There are two tables, patient data and lab data (one to many).

I am ultimately trying to calculate 3 pieces of information:

1) Nadir Value - the lowest value before a treatment failure is determined. The progression of values can have small increasing fluctuations like the example below.

2) Nadir Date - the date of the Nadir Value.

3) Failure Date - the first date the lab value is >=(the nadir value + 2). Once a failure date is determined there is no need for the calculation to continue because additional treatment could be given which would create lower lab values. These values are not considred to be a nadir because they occurred after the treatment failure date.

A nurse will only enter the lab value and the lab date. A new lab value and date will be entered each time the patient comes back for follow up.

A FMP instructor for a class I attended provided the 6 calculations in the sample database but they are not working for me.

Any ideas on what will work?

The following is an example of Patient 1 in the sample database and how the calculations should result.

Date…………Value……Nadir……NadirDate……FailureDate

6/6/05…………3.4………3.4…………6/6/05

7/11/05………2.1………2.1………7/11/05

7/14/05………3.2………2.1…………7/11/05

8/4/05…………4.6………2.1…………7/11/05

8/31/05………2.1………2.1…………7/11/05

12/1/05………1.1………1.1…………12/1/05

3/9/065………2.7………1.1…………12/1/05

11/30/06……5.2………1.1…………12/1/05……..11/30/06

12/8/07………0.01……1.1…………12/1/05……..11/30/06

4/20/07………7.9………1.1…………12/1/05……..11/30/06

I would then want a single output of the Nadir Value 1.1, Nadir Date 12/1/05 and the Failure Date 11/30/06 to be displayed along with the lab date and values.

Thanks to anyone who responds. This has really floored me.

Failure_Calculation.zip

[eos]

The following is an example of Patient 1 in the sample database and how the calculations should result.

Date…………Value……Nadir……NadirDate……FailureDate

6/6/05…………3.4………3.4…………6/6/05

7/11/05………2.1………2.1………7/11/05

7/14/05………3.2………2.1…………7/11/05

8/4/05…………4.6………2.1…………7/11/05

8/31/05………2.1………2.1…………7/11/05

12/1/05………1.1………1.1…………12/1/05

3/9/065………2.7………1.1…………12/1/05

11/30/06……5.2………1.1…………12/1/05……..11/30/06

12/8/07………0.01……1.1…………12/1/05……..11/30/06

4/20/07………7.9………1.1…………12/1/05……..11/30/06

I would then want a single output of the Nadir Value 1.1, Nadir Date 12/1/05 and the Failure Date 11/30/06 to be displayed along with the lab date and values.

Thanks to anyone who responds. This has really floored me.

I'm a bit confused: based on your example data, shouldn't the desired result actually be:

Nadir Value 2.1, Nadir Date 7/14/05 and Failure Date 8/4/05,

since 4.6 (8/4/05) *is* >= 2.1 (7/14/05) + 2?

Oliver

[eos]

Anyway, here's an idea. I built a sample file based on your attachment and took the liberty to remove all of the by now unnecessary summary fields; their job is (mainly) done by two relationships and an auto-enter calculation. Here's the rundown:

1) In Lab data, create a new number field isFailureOne with an auto-enter calculation; it gets filled with a 1 if the labValue is greater/equal than the existing nadir OR failure has already been tested true in the past (see step 4 below)

BEWARE: you can't add the second test (after the OR) right away; first, you must use this here field to establish the new relationship, then create the field from step 4, and THEN you can use this field to add the second half of the test.

2) create two more relationships from Patients to LabData; the first one relates to all lab records for this patient not flagged with a failure one (beforeFailure), the other relationship works in reverse (Failure).

I almost forgot; for this you need to create also a new number field in Patients, set auto-enter value to 1 and fill all existing patient records with a 1 by using "replace field contents"; this is the legendary helper constant)

3) in the Patients table, create a Nadir calc field which calculates the min labValue of all BeforeFailure LabData records.

4) also in Patients, create a calc field which only holds a value if failure is true, like isValid ( onFailure::PTNum )

5) use this value as boolean which a) serves as an additional criterion for the auto-enter calculation in the LabValue table (second test in step 1), and : can be used for the calculation that compiles the desired result text

6) now you have all information in place:

a) the nadir value is the Min() value of all lab entries not flagged as failure; since after first failure identification all subsequent lab entries will be flagged as failure, this value won't change anymore, even if a smaller value occurs at a later date

the nadir date is the Max() date value of all lab entries not flagged as failure; same system as described above

c) the failure date is the Min() date value of all lab entries flagged as failure

You can define new fields to calculate these values individually, and/or build a text string that presents them in any desired format (test on failureIsTrue to have it only compiled if... well, failure is true ;-)

This solution doesn't work retroactively as it is, but in the normal "workflow" – lab entries are entered in their chronological order – it should work, as far as I can see; I emulated this by erasing some of your pre-entered data for Patient no 1 and then re-entering them. For larger existing data bases where it would be tedious to do this by hand it should be possible to achieve the desired results by using a script that loops through the lab records on a per-patient basis in chrono order and sets the required flags.

Hope this helps!

Oliver

Failure_Calculation_modifiedByEOS.fp7.zip

[comment]

I didn't examine either of the files too closely, except to check the results - and I think both are producing incorrect values. I also believe that the results should not depend on the order in which data is entered. If a previously entered value needs to be corrected, for example, the solution should still produce the correct results reflecting the correction.

It would probably be easier to do this with a custom function or two, but since that option is not available, relationships and calcs will have to do.

Failure.fp7.zip

[eos]

I didn't examine either of the files too closely, except to check the results - and I think both are producing incorrect values. I also believe that the results should not depend on the order in which data is entered. If a previously entered value needs to be corrected, for example, the solution should still produce the correct results reflecting the correction.

It would probably be easier to do this with a custom function or two, but since that option is not available, relationships and calcs will have to do.

My solution produces correct results, and you can even correct a previously entered value - as long as there are no "future" entries. I think the only clean solution would be to do the value entry/correction by a script which also checks ALL existing entries and then calculates the correct results

Edited December 12, 2007 by Guest

[Pixbo]

Youre right. Goes to show no matter how much I proof read I can miss something. I thought I changed that 4.6 value to 4.0 to better illustrate my point of how it fluctuates. Thanks, eos!

Also, I am converting a Microsoft Access database which already has over 2k patient entries and 16k lab entries. Labs do get lost and can sometimes be entered out of order. And as my example unwitingly showed, corrections sometimes need to be made. ;-)

I know we are upgrading to version 9 but these things happen at a snail's pace and we cant wait for when that might happen. Thank you, Comment and Eos, for the explanations and the detailed help. I will work on it today with our existing data and let you know how it goes.

Edited December 12, 2007 by Guest

[comment]

My solution produces correct results

Well, I don't know. I got different results, so one of the methods must be wrong. I'll leave it to Pixbo to do the checking. In all fairness, I didn't check my method too extensively either, and I wouldn't use it for something that may affect people's health without re-examining it over and over, to guard against potential pitfalls.

I think the only clean solution would be to do the value entry/correction by a script which also checks ALL existing entries and then calculates the correct results

I strongly disagree. All the data required to calculate the results is already in the system. A calculation by script by necessity needs to store its result. This creates a redundancy, and opens an opportunity for a discrepancy.

[eos]

Well, I don't know. I got different results, so one of the methods must be wrong. I'll leave it to Pixbo to do the checking. In all fairness, I didn't check my method too extensively either, and I wouldn't use it for something that may affect people's health without re-examining it over and over, to guard against potential pitfalls.

I strongly disagree. All the data required to calculate the results is already in the system. A calculation by script by necessity needs to store its result. This creates a redundancy, and opens an opportunity for a discrepancy.

I'm with you on the necessity of extensive reviewing of any system that is employed in critical environs, such as hospitals, labs etc.

On the other hand, I strongly disagree about your comparison of calc fields vs scripts and the misleading (in this context) statement that a script needs to store its result. Well, a calculation field also stores its results (I take it you're not talking about indexing etc. when you say "store", but just about the need to put data somewhere), and not by necessity, but by its very nature.

From a structural point of view, I see no difference between having a calculation field being populated by its own inherent logic, or populating a "normal" (non-calculation) field by way of a script holding the logic. In both cases you use a container - a field - to store data which is derived from the "original" data; also in both cases, you do it only once per record, so there is no redundancy, and also no discrepancy (to what?).

[comment]

I am afraid you are missing my point. Whether a calculation field is set to stored or not is unimportant in this context. What is important is that a calculation field always returns the correct result.

A field set by script can can be trusted to have a correct result only immediately after the script has run. A second later the result is already potentially incorrect.

In a case like this, where the data is coming in from multiple records, the calculation field will be forced to unstored - and for a good reason (see more here). This way, any time the result is required for any purpose, it will recalculate using the most current input data - without requiring the user to take any further action.

[eos]

I am afraid you are missing my point. Whether a calculation field is set to stored or not is unimportant in this context. What is important is that a calculation field always returns the correct result.

A field set by script can can be trusted to have a correct result only immediately after the script has run. A second later the result is already potentially incorrect.

In a case like this, where the data is coming in from multiple records, the calculation field will be forced to unstored - and for a good reason (see more here). This way, any time the result is required for any purpose, it will recalculate using the most current input data - without requiring the user to take any further action.

I explicitly stated that I did *not* assume you were talking about "store" as in "index", so this point is moot; it's also common FM knowledge that calculations involving related or other unstored fields can't be stored... but seeing your contribution to this other thread, I think I can see where you come from, and where you're going... ;-)

Also, I suggested in one of my earlier posts that data entry itself is done by script, which guarantees that the script result is also valid after every entry, until the next entry – which again is done via script etc. This also gives the developer the chance to implement all sorts of error and plausibility checks etc. (As you mentioned, it's kind of vulnerable in a multi-user scenario where records might be locked, but then again you have to know your client and their setup and what's workable and what's not.)

The question of redundancy isn't really important here; opposed to the scenario you employ in the other thread, here it's not about supplying child records with data from the parent record and storing them there (which would rise the question as why to use a relational database in the first place), but analyzing existing data to calculate new data in one place. The fact that a certain date is the failure date is something else than the date itself.

By the way, you might want to have these "new" data in stored form to quickly find patients with certain attributes/results in a large database; how'd you do it with a calculation?

Anyway, let's rest this discussion for now; I think we will have a hard time coming to an agreement by exchanging posts, it would be much easier to do this in person. But thanks for your "other" perspective and for the link to the other thread; makes for an interesting read.

[Pixbo]

This is the first time I have dealt with table occurrences and it's really opened up new doors for us! The format provided by Comment works the best. I ran it through all the possible manipulations that could happen in the work place and it holds up. The only problem I had with what Eos provided was when I added new records it would sometimes take the first value as a failure even though it would calculate the correct nadir and date? I'm too new with FMP to figure out how to set up a script for it. The detailed explainations were extremely helpful in understanding the use for occurrences.

However, your discussions have inspired us around here to look into the best way to use scripts and to see other ways in which we can utilize the table occurrences. Thank you for that!

Rest assured the data output from these calculations will in no way determine treatment for patients. This data is recorded retrospectively and is only used for journal publications and then the data is looked at with a statistical fine toothed comb! I'm trying to cut out the amount of user error brought on by the user eye balling what could be evaluated through calculations.

Eos and Comment, Thank you very much your detailed help!

Happy holidays and a wonderful New Year!

Edited December 13, 2007 by Guest