asked on

Display the prior date when grouping in Crystal Reports

Hi, I have a report which reports on all the date's that a patient has had office visit. The data is limited using a parameter for a start and end date for the contact date. I need to display the most recent contact date (as defined in the parameter) and the previous date.

I am using the MAX function for the Recent Contact Date and that is working,

I really need help displaying the Prior Contact Date field.

Below is an example of the data. Parameter contact date from 3/1/14 to 4/1/2014

PATIENT_ID CONTACT_DATE
1234 4/01/2014
1234 3/15/2014
1234 3/04/2014
4567 4/01/2014
4567 3/12/2014
8910 3/05/2014
1247 3/18/2014

I would like to display a single line for each id:

PATIENT_ID      Recent Contact Date       Prior Contact Date
1234      4/01/2014       3/15/2014
4567      4/01/2014       3/12/2014
8910      3/05/2014
1247             3/18/2014

Any ideas how I can accomplish this?

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

Where are you displaying the single line?

mlmcc

James0628

FWIW, the Next idea may not work if there could be more than one record for the same date. Next just gives you the value from the next record, so if the max date is first and there is more than one record with that date, Next may just give you the same date (from the next record). It depends on exactly where you use it.

James

jul_to_cool

ASKER

I am grouping by the patient id and sorting the records in descending order in the details. The single line can be displayed in group 1.

I tried using NEXT({DateFIeld}) however I am experiencing the issue that James describes in his post. When there is only 1 date for a record then the next formula displays the next date for the next record rather than a null value.

For example id 8910 has only 1 contact date, the next contact date should be null. It shows as the contact date for the next record, 1247, as 3/18/2014.

PATIENT_ID CONTACT_DATE Next Contact Date
8910 3/05/2014 3/18/2014
1247 3/18/2014 last record on the report displays a blank

SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jul_to_cool

ASKER

Thanks! It works beautifully!

James0628

mlmcc should probably get some, if not most, of the points. He suggested using Next. I just added to that. You can use the "Request Attention" link to ask to have the question re-opened, and then split the points between his first post, and my post.

James

jul_to_cool

ASKER

I have been asked to determine the days between the recent contact date and prior contact date and then give a summary for group 1 regarding the # of patients with a contact greater than 1, 6 and 12 months. I was able to use the DateDiff function to get the number of months between the two dates (recent date uses a Maximum function and prior using Next function), I also changed the formula to identify as a date instead of a string.
However but I am not able to use the Datediff formula for months within any of the formulas, running totals, ect. I am getting a error message..."This formula cannot be used because it must be evaluated later".

I do not know of another way to get this info, any help is appreciated!

Below is the original next formula by James0628...

if Next ({Patient_ID}) = {Patient_ID} then
CStr (Next ({Contact_date}), "M/dd/yyyy")
else
""

James0628

You should maybe start a new question for that, since it seems like a pretty significant departure from the original question.

And while I'm here, I'll repeat what I said before, that mlmcc should probably have gotten at least some of the points for this question, since he suggested using Next.

Getting back to the new question, FWIW, I don't think DateDiff is the problem. I'm guessing that you're trying to do a summary on a formula that uses Next or Maximum, and CR won't do that. I'm not sure exactly what you're trying to do, but you may have to use formulas and variables to create your own totals.

James

jul_to_cool

ASKER

Thank you. Please re-open the question so I may award some points to mlmcc.

James0628

There's no guarantee that a moderator will see your post. You can use the Request Attention link to formally ask to have the question re-opened.

James

James0628

My post # 39979284 was certainly not the solution to the question. I'm going to ask that the question be re-opened, and hopefully you'll come back and split the points between mlmcc's first post (where he suggested using Next) and my post # 39978093 (where I suggested a possible solution to a problem that you had when you used Next).

James