Display multiple values from same field

[wgechter]

We have a field of laboratory values and another field for the date the lab was done. What they are asking me to pull is the most recent lab value and the lab value right before the most recent, within the last year. Currently the formula looks like this:

if {LAB_NAME} = "HGB A1C" and
(not isnull({LAB_RESULT})) and
{D_LAB_DRAWN} in (currentdate - 365) to CurrentDate
then val({LAB_RESULT});

How can I pull the next-to-most-recent and most recent lab values? Thanks for everyones help.
Wendi

 

[lbass]

Try a record selection formula like:

{LAB_NAME} = "HGB A1C" and
(not isnull({LAB_RESULT})) and
{D_LAB_DRAWN} in (currentdate - 365) to CurrentDate

Sort by {D_LAB_DRAWN} ascending, and then drag {LAB_RESULT} into the footer (report or group, if you have one). Then create a formula {@previous}:

previous({LAB_RESULT})

...and place that in the footer as well. Repeat this for the date field.

-LB

 

[wgechter]

Thanks! I cannot make that a record selection because I already have a population base, but it is a formula called HGB A1C. I did a sort on D_LAB_DRAWN which is also a formula and put the formula in the footer. Then I created a formula:
previous({@HGB A1C})

Nearly the same thing, but unfortunately it came back with 0. Nothing showed up. I do know that some of these have more then one lab in a year. Anyone? Thanks,
Wendi

 

[lbass]

In future posts, you should use {} around database fields and formulas should be displayed like {@formulaname}. You should also share the contents of your formulas and your record selection formula. Please provide some sample data at the detail level so that we can see how fields are "behaving," and then someone can probably help.

-LB

 

[wgechter]

Thanks all keep all that in mind for the future....

I have a formula {@HGB A1C}:
if {LAB_NAME} = "HGB A1C" and
(not isnull({LAB_RESULT})) and
{D_LAB_DRAWN} in (currentdate - 365) to CurrentDate
then val({LAB_RESULT});

And a formula {@date}:
if {MRRV99.LAB_NAME} = "HGB A1C" and
(not isnull({MRRV99.LAB_RESULT})) and
{MRRV99.D_LAB_DRAWN} in (currentdate - 365) to CurrentDate
then {MRRV99.D_LAB_DRAWN};

New formula {@previous}:
previous({@HGB A1C});

I have two groups setup so all of these formulas are put in a group footer. I also have it set to sort by D_LAB_DRAWN.

Values in fields:
Patient Name {@HGB A1C} {@date} {@previous}
Bob Jones 7.3 6/15/03 0
Kelli Smith 5.6 5/18/03 0
Harry Clark 7

Sometimes when they have no lab the {@previous} displays 6 or 7, but it should display nothing at all. I hope that gives a better explanation. Thanks,
Wendi

 

[lyanch]

Ah... I see the problem. The formula {@Previous} probably just uses previous( {@HGB A1C}) which will always show the previous value, even if the group has changed. This will be true for all groups that have only one record. To correct this change {@Prvious} to:

if <<whatever field you group on >> = previous(<<whatever field you group on >> )
then
previous({@HGB A1C})
else
0

insert the actual field you group on (not the group header) where is says <<whateve3r field you group on >>



Lisa

 

[Ngolem]

let us get some mundane details sorted out.

HOW IS YOUR REPORT STRUCTURED??

I don't have a complete understanding based on the limited information you have provided.

Show us the groupings and where your formulas are presented.

***********************************
Thanks all keep all that in mind for the future....

I have a formula {@HGB A1C}:
if {LAB_NAME} = &quot;HGB A1C&quot; and
(not isnull({LAB_RESULT})) and
{D_LAB_DRAWN} in (currentdate - 365) to CurrentDate
then val({LAB_RESULT});

And a formula {@date}:
if {MRRV99.LAB_NAME} = &quot;HGB A1C&quot; and
(not isnull({MRRV99.LAB_RESULT})) and
{MRRV99.D_LAB_DRAWN} in (currentdate - 365) to CurrentDate
then {MRRV99.D_LAB_DRAWN};

New formula {@previous}:
previous({@HGB A1C});

I have two groups setup so all of these formulas are put in a group footer. I also have it set to sort by D_LAB_DRAWN

*****************************

So you have changed from a record selection formula to using these as grouping formulas??? Do I have that correct???

If so these are not very good grouping formulas

Why? because there is no &quot;Else&quot; clause to the IF-then I don't think Crystal can group on a NULL.

Your formula {@date} is based on {MRRV99.D_LAB_DRAWN} so sorting on this field is meaningless.

I don't understand why you cannot do a record selection formula

******************
Thanks! I cannot make that a record selection because I already have a population base, but it is a formula called HGB A1C. I did a sort on D_LAB_DRAWN which is also a formula and put the formula in the footer. Then I created a formula:
previous({@HGB A1C})

*******************

Give us the record selection formula that you use...how the report is grouped...a sample of the data and expected output

Then you will get a decent answer....


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[wgechter]

I believe I am giving a good output so I am sorry for that. Obviously I am not communicating correctly in Crystal Reports terms and since I am not a CR expert that is my fault. I do believe that I am being sincere and trying my best..get off my back!!

My record selection is not a problem and too complex for me to write but it's not my problem. I get what I want on my records so that is done...It finds all patients with certain problem codes, with certain characters in the first name field, attached to certain doctors, with a certain patient ID. It's not a problem.

Group 1 = DOCTORS
Group 2 = Patient ID (so that I don't get duplicates)(suppressed)
Sorted by {@date}

Group Footer 2 has all my details including the formulas above: name, sex, phone number, {@hgb a1c}, {@date}, {@previous}

I have changed my {@previous} formula to look like this:
if {MRPA99.PAT_EXT_ID} = previous({MRPA99.PAT_EXT_ID})
then previous({@HGB A1C})
else 0;

I want the output to look like this in one row:
name, sex, phone number, hgba1c value, date of hgba1c, previous hgba1c, date of previous hgba1c

Is this clear enough!!!!???? I obviously can't get it right and keep trying. Please be nice when asking me what you want.

 

[lbass]

Wendi,

That's a bit better, but what we really need to see is some detail level data. The issue, at least as I see it, is that your formulas {@HGB A1C} and {@date} have many criteria, which implies that your detail data also contains a variety of data that you are not interested in. For example, from your formulas, I would guess we might see data like this at the detail level:

Lab_Name Lab_Result D_Lab_Drawn {@HGB A1C} {@date}
HGB A1C 6.5 4/15/02 0
HGB A1C 7.3 7/1/03 7.3 7/1/03
FCP B2D 4.5 8/3/03 0
ABC X4Z 3.0 8/3/03 0
HGB A1C 6.2 8/25/03 6.2 8/25/03
HGB A1C (null) 9/15/03 0

You might try grouping on {@HGB A1C}, ascending, and then also insert a group on {@date}, ascending (not on the date field itself). I think this will order your data like:

Lab_Name Lab_Result D_Lab_Drawn {@HGB A1C} {@date}
HGB A1C 6.5 4/15/02 0
HGB A1C (null) 9/15/03 0
HGB A1C 7.3 7/1/03 7.3 7/1/03
HGB A1C 6.2 8/25/03 6.2 8/25/03

Then you could use my earlier suggestion and drag {@HGB A1C} and {@date} into the group footer, and also add {@previousdate} and {@previousHGBA1C} to the footer, adding the if-then clauses to ensure you are not reaching back to a previous patient.

If this still doesn't work, please copy some sample detail level data into your post so we can really see what's going on.

-LB

 

[wgechter]

Thanks LB,
My problem lies trying to get the previous HGBA1C lab result. Sample of what I would like to get:

LAB_NAME {@HGBA1C} {@date} {@previoushgba1c} {@previousdate}
HGBA1C 7.3 5/14/03 6.5 4/10/03
HGBA1C 8.6 11/5/03 10.2 6/30/03

Where each of those is a different patient. I am not having trouble displaying just the HGBA1C (vs other labs) for the patient. All I am having trouble doing is displaying the HGBA1C they received right before the last one they received.

I created two more groups, Group 3 = {@hgba1c} and Group 4 = {@date} and put all details ({@hgba1c}, {@date}, {@previous},etc) in group footer 4.

I changed my {@previous} to:
if {@date} = previous({@date})
then previous({@HGB A1C})
else 0;

The data I am getting now is:
LAB_NAME {@HGBA1C} {@date} {@previoushgba1c} {@previousdate}
HGBA1C 7.3 5/14/03 7.3 5/14/03
HGBA1C 8.6 11/5/03 8.6 11/5/03

I would like it to look like the table in my second paragraph. I'll keep working on it. I really appreciate everyone's help. Thanks again,
Wendi

 

[lbass]

Actually, I see I made a mistake. I meant to suggest that you group on {table.lab_name} not {@HGB A1C} and you could just sort on {@date} ascending instead of creating a group, although grouping on it doesn't really hurt. But your results belong in the Group 3 (Lab Name) footer, not the group 4 footer.

Your previous formulas should be:

//{@previousHGBA1C}:
if {MRPA99.PAT_EXT_ID} = previous({MRPA99.PAT_EXT_ID})
then previous({@HGB A1C})
else 0;

//{@previousdate}:
if {MRPA99.PAT_EXT_ID} = previous({MRPA99.PAT_EXT_ID})
then previous({@date})
else date(0,0,0);

Using my earlier example, this should give you in the details section:

Lab_Name Lab_Result D_Lab_Drawn {@HGB A1C} {@date}

HGB A1C 6.5 4/15/02 0
HGB A1C (null) 9/15/03 0
HGB A1C 7.3 7/1/03 7.3 7/1/03
HGB A1C 6.2 8/25/03 6.2 8/25/03

And the following in the group footer:
Lab_Name {@HGB A1C} {@date} {@previousHGBA1C}{@previousdate}
HGB A1C 6.2 8/25/03 7.3 7/1/03

You will have other lab name groups appearing that you can suppress if need be by going to format section->group header and group footer->suppress->x+2:

{table.lab_name} <> &quot;HGB A1C&quot;

Sorry for the earlier error.

-LB