Running Total--Formula Code to evaluate only the LAST Detail record?

[gal]

C.R. 8.5: Background: The Diagnosis History table contains three record types reflecting data at the time of Admission, Update, or Discharge. A patient may be admitted with a substance abuse (SA) diagnosis that may remain or be lost upon Update or Discharge. Or he may acquire an SA diagnosis after the initial Admission. There may be multiple records of any of the three types, including Admission records. Update or Discharge records may or may not exist.

<b>I select only Admission type records from the Diagnosis History table, then Left Outer Join to the Alias of the same table to select Update and Discharge type records.</b>

Several formulas evaluate Diagnosis Changes between the time of Admission and the LAST History record. For each patient with a specific Diagnosis Change, I get a DISTINCT COUNT of the Patient; and calculate the number of DAYS the patient is in the hospital. The number of PATIENTs and DAYS are reported at the patient level, and Sub-totaled at three Groups using Running Totals. This gets tricky because of multiple History records.

<u>A formula looks for lost SA Diagnosis.</u> In one case, a patient starts out with an SA diagnosis. In the next record, the SA diagnosis is removed. The final record for that patient again shows an SA diagnosis. My current results show a change from SA to No SA because of that middle record. In reality, the formula should only reflect the difference between the initial (Admission) History record and the Last History record.

<b>What code can I use in my Running Total formulas to only evaluate the LAST History record?</b>

Selection formula:
{ history.diagnosis_type} = "A" and
{ history _1.diagnosis_type } <> "A" and
{ history. diagnosis_date} <>history_1. diagnosis_date}

Your kind assistance will be much appreciated.

 

[lbass]

This is difficult to visualize. Please show the contents of the formulas you are currently using and explain how you have set up the running totals. It would help to see some sample data.

My initial thoughts are that you should be able to use a datediff formula like:

datediff("d",minimum({history.date}), maximum({history.date})

Or in a running total, you could add a clause to the evaluation formula like:

{table.patientID} <> next({table.patientID})

...assuming an ascending sort on the history date. BUT, these are just wild guesses without more information.

-LB

 

[gal]

Thank you, LBass. One typical formula counts PATIENTS who are admitted with an SA diagnosis and subsequently lose it:

Running Total Name: RT1_SA _Removed:
Field to summarize: @PatientID
Type of summary: distinct count
Evaluate Use a formula: {@DX_SA_ Removed} >= 1
Reset on change of Group: Group#1

The next formula counts the DAYS the patients are in the hospital for the same patients:

Running Total Name: RT1_DAYS_SA _Removed:
Field to summarize: @DAYS_SA_Removed
Type of summary: sum
Evaluate On Change of field: @PatientID
Reset on change of Group: Group#1

I believe I need to base the evaluation on the condition of the only the last sequential detail record in the PatientID group, but don't know how to code it.

 

[lbass]

As I said earlier, you would use the following in the evalution section of the running total, assuming you have your records sorted so that the last history record is displayed last in the patient ID group:

onlastrecord or
{table.patientID} <> next({table.patientID})

In your first sample rt, you would add this phrase to your existing evaluation formula:

and (
onlastrecord or
{table.patientID} <> next({table.patientID})

In your second one, change the evaluation to use a formula and add the clause there.

-LB

 

[gal]

Thank you so very much, LBass. It's just what was needed. Have a great evening!