Results based on date

[wgechter]

I have a field, {lab_name}. My formula, @HDL, looks like this:

if {LAB_NAME} = "HDL CHOL" and
(not isnull({LAB_RESULT})) and
{D_LAB_DRAWN} in (currentdate - 424) to CurrentDate
then {LAB_RESULT};

This displays the lab result for HDL Chol. I also have one exactly the same, @hdl date, displaying the date the lab was drawn. This is great, but I get all labs in the last 424 days. I want the last one. So I put the formula in the details and inserted a summary for max of @hdl and max of @hdl date. Doesn't work if I have

hdl 8 on 7/20/05
hdl 7 on 10/8/05

What I get is 8 on 10/8/05 being they are both the max. How do I get the max date but associated lab value? Thanks,Wendi

 

[lbass]

Do you have the option of limiting the report to HDL records and to that date range? If so, you could create a record selection formula like:

{table.LAB_NAME} = "HDL CHOL" and
{table.D_LAB_DRAWN} in currentdate - 424 to CurrentDate

Then you could insert a group on patient ID (I'm guessing you are pulling this for multiple patients), and then go to report->selection formula->GROUP and enter:

{table.D_LAB_DRAWN} = maximum({table.D_LAB_DRAWN},{table.patientID})

This will display the most recent record for each patient.

-LB

 

[synapsevampire]

Successful posts tend to share basic technical information:

Crystal version
Database/connectivity used
Example data
Expected output

You've shown a formula, presumably in the details section, but more importanly, you're onloy showing what DOESN'T work, not what you have and what you need.

So do you want the maximum date and it's associated value?

Go into the Report->Selection Formulas->Group and place:

{table.date} = maximum({table.date})

Now place the hdl in the details.

This probably won't work and you'll come back and state that you need it by some entity, so please try to think in terms of requirements in your posts, rather than showing what you don't want.

-k

 

[wgechter]

Thank you lbass for your response.

Vampire, I specifically stated to your question, "So do you want the maximum date and it's associated value?" in my last line, "How do I get the max date but associated lab value?" So YES, I would like max date and associated lab value...again. You stated, "You've shown a formula, presumably in the details section" which I will then have to say read my post again, "So I put the formula in the details and inserted a summary..." I'm on Crystal version 10. The other information is irrelevant to creating a formula or grouping in this post.

Lbass, unfortunately I cannot limit the report to HDL. I am also pulling items such as HGB, LDL all in the same report the same way. I thought that if I could get HDL's max date and 'associated' lab value I could apply those rules to the other labs as well. I do have two groups, Patient_Last_name > Patient_ID so that it alphabetizes them by last name. My max of @hdl date is located in the group footer of patient ID.

Any more information that is needed I will supply but I can't think of everything that someone might want or need, I am not a Crystal expert. Thanks for your help, Wendi

 

[lbass]

Can you limit the report by using the date in the record selection criteria? If so, you could insert another group on {table.labname}. Then sort in descending order on the date field. You could then drag the detail fields into the labname group header and suppress the detail.

-LB

 

[wgechter]

Unless I am misunderstanding, I tried this but I took out the {table.D_LAB_DRAWN} = maximum({table.D_LAB_DRAWN},{table.patientID})in the group selection
and it gave me both results in this order:

7 on 10/8/05
8 on 7/20/05

When I leave the group selection in, it worked on some patients but not on others.

I am looking to display only the 7 on 10/8/05 since it is the most recent. I'm not sure why some worked and others didn't

 

[lbass]

I was suggesting that you use the following in report->selection formula->RECORD:

{table.D_LAB_DRAWN} in currentdate - 424 to CurrentDate

Then insert a group on {table.LabName} and go to report->sort records and add {table.D_LAB_DRAWN} as a sort field, descending. Then drag the detail fields into the labname group header and suppress the details.

-LB

 

[wgechter]

Yep, did that. Here's what I get. If I group by {labname} I get all labs, which I'm only looking for a few. (but you didn't know that) but it is working as you say. So can I now create a formula to do my group instead of just {labname}? I'm not sure what the formula should look like? I need HGB, HDL, LDL, and DLDL only in my lab list instead of all labs. Thanks for your help!

 

[lbass]

Again, I would limit this in the record selection formula, using:

{table.labname} in ["HGB","HDL","LDL","DLDL"] and
{table.D_LAB_DRAWN} in currentdate - 424 to CurrentDate

-LB

 

[wgechter]

So, I did a formula @labgroups:
if {LAB_NAME} = "HDL CHOL"
or {LAB_NAME} = "LDL CHOL"
or {LAB_NAME} = "DLDL"
or {LAB_NAME} = "HGB A1C"
then {LAB_NAME};

And I grouped on this formula, so it works, but I have two more questions.

It appears on 4 seperate lines for each lab result, can it all be on one line?

What is the reason for putting it in the group header instead of footer?

 

[lbass]

I can't really tell if you are responding to me. Why not use the record selection formula to limit the records returned to your report instead of doing a special grouping formula?

You can put your fields in the header or footer, but then you will have to adjust the date sort accordingly.

You should have mentioned that you wanted a horizontal display of the test results up front--because you can't do that with the current approach.

Please identify the exact table/field names that you are working with (so far you have only mentioned the field names). Then I will suggest how to use a command to limit the records so that you can insert a crosstab.

-LB

 

[wgechter]

I didn't know it was going to be this complex so I didn't think it was worth the time. I did get what I want. I could limit in my record selection, that's fine I just didn't (not an expert) Specifically, without trying to make things simple(which I realize didn't work) here's my record selection currently:

{TABLE1.PROBLEM_NAME} like ["*NIDDM*", "*DIABETES*", "DIAB", "DIABETES MELLITUS", "DM"] and
not({TABLE1.PROBLEM_NAME} like ["DIABETES ISIPIDUS", "DI","*FH*", "FAMILY HISTORY DM", "*FAMILY*"]) and
{TABLE2.USUAL_PVID} in ["AB2", "CW1", "DD1", "DS1", "JB1", "JH1", "JQ1", "KP1", "MM1", "MP1", "PM1", "PS1", "SS2"] and
{TABLE3.LAB_NAME} in ["DLDL", "HDL CHOL", "HGB A1C", "LDL CHOL", "MICROALB/CR RATIO", "OPHTHALMOLOGIST"] and
not ({TABLE2.PAT_FIRST_NAME} like ["*decea*", "*disch*", "*trans*"])

I changed it to record select those lab names. And I have a group on lab name and a record sort on lab date descending and it's all in my groups header. Anything else?