Looking for current and prior field values in the same view

[gk17]

We have a patient view that has the following fields:

Last Name & First Name which will be concatenated via a formula
Medical Record Number – unique for each patient
Exam Code
Date of Exam

Trying to put together a report that will do the following:
- If a patient has an MARPT exam done, check to see when is the last MASCDIBI exam that was performed. If MARPT is done, they will definitely (or at least should) have MASCDIBI done in the past.
- If it’s possible to add an optional way to search for xx amount of days in the past that would be good also as they may have it done in the past (over a year ago) which we will want excluded.
- The report can have all the 4 fields mentioned above.

Layout can be something like:

Group Header – Patient Name & Medical Record Number
Details a – Exam Code (MARPT) & Date of Exam
Details b – Exam Code (MASCDIBI) & Date of Exam

Thanks.

 

[gk17]

Hi,

Can anyone help me here? Are any additional information needed? I can't figure out how to search for the last exam (MASCDIBI assuming it even exists, if not, exclude these results) within the past 30 days (from MARPT date of exam).

Output will be something like:

Patient1 1234 MARPT 11/1/2013
Patient1 1234 MASCDIBI 10/15/2013

Thanks.

 

[pmax9999]

There are a few different ways to tackle this. The way I did it in testing is as follows:
[ol 1]
[li]Add {Table.Exam_Date} and {Table.Exam_Code} to the Details section[/li]
[li]Create Group (1) on {Table.Medical_Record_#} - this could be grouped on a formula that concatenates the Patient's last name with the Record # to get the listing in alphabetical order[/li]
[li]Create Group (2) on {Table.Exam_Code}. GH2 and GF2 can be suppressed.[/li]
[li]Create a Record Selection formula of:

{Table.Exam_Code} in ["MARPT", "MASCDIBI"]

[/li]

[li]Create a Group Selection formula of:

DistinctCount({Table.Exam_Code}, {Table.Medical_Record_#})= 2 and   //ensures both Exam_Codes exist
{Table.Exam_Date} = Maximum({Table.Exam_Date},{Table.Exam_Code})    // picks only the latter date if multiple dates exist for either Exam_Code

[/li]

Hope this helps

Cheers
Pete

 

[gk17]

Hi Pete,

Thanks for your help again.

Definitely made a lot of progress from what I had previously. I'm using a date range in the record selection. If the MASCDIBI exam code doesn't fall in that date range, I get no results returned. Is there a way to use the exam date from MARPT and "recursively" search the last 30 days under that patient for MASCDIBI?

For example:
- I search from 11/1/2013 to 11/30/2013
- MARPT exam date is on 11/10/2013 but MASCDIBI was on 10/20/2013
- Can the report be modified to search last 30 days under that 11/10 date for MARPT to look for MASCDIBI?

 

[pmax9999]

OK, that is going too require a change of direction. Try this:
[ol 1]
[li]Add the table to the report a second time (CR will give it an alias of TableName_1), and join the 2 instances of the table on the Medical Record # field;[/li]
[li]Create a group on {Table.Medical_Record_#}[/li]
[li]Split the Details section into 2 sections. Add {Table.Exam_Date} and {Table.Exam_Code} to section A, and {Table_1.Exam_Date} and {Table_1.Exam_Code} to section B.[/li]
[li]Add the following Record Selection formula:

{Table.Exam_Code} = "MARPT" and
{Table_1.Exam_Code} = "MASCDIBI"

[/li]
[li]Add the following Group Selection formula

{Table_1.Exam_Date} = Maximum ({Table_1.Exam_Date},{Table.Medical_Record_#}) and
{Table.Exam_Date}   = Maximum ({Table.Exam_Date},  {Table.Medical_Record_#}) and
Maximum ({Table_1.Exam_Date},  {Table.Medical_Record_#}) > Maximum ({Table.Exam_Date},  {Table.Medical_Record_#}) -30

[/li]
[/ol]

Does that give you what you need?

Cheers
Pete

 

[gk17]

Hi Pete,

That worked out perfectly. Thanks again for all your help with another issue.