Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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?
0
jul_to_cool
Asked:
jul_to_cool
  • 6
  • 4
  • 2
2 Solutions
 
mlmccCommented:
Are you sorting the records in descending date order?

If so you can use NEXT({DateFIeld}) get the next date

mlmcc
0
 
mlmccCommented:
Where are you displaying the single line?

mlmcc
0
 
James0628Commented:
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jul_to_coolAuthor Commented:
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
0
 
James0628Commented:
Technically, that's a different issue, but that one is fairly easy to handle.

 You can use Next ({Patient_ID}) to make sure that the next record is for the same patient.  You just have to decide what you want to do when the next record is for a different patient.

 If you want the field to be blank, you could use something like:

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


 James
0
 
jul_to_coolAuthor Commented:
Thanks!   It works beautifully!
0
 
James0628Commented:
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
0
 
jul_to_coolAuthor Commented:
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
  ""
0
 
James0628Commented:
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
0
 
jul_to_coolAuthor Commented:
Thank you.  Please re-open the question so I may award some points to mlmcc.
0
 
James0628Commented:
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
0
 
James0628Commented:
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now