13 Month Rolling History
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1998
Printed Date: 05 May 2024 at 6:37am
Topic: 13 Month Rolling History
Posted By: PAlexander
Subject: 13 Month Rolling History
Date Posted: 08 Jan 2008 at 6:59am
|
Greetings:
This is my first post and I have a question. I wouldn't be here if I didn't have a question, right?
I need to generate a monthly report that consists of patients that have not scheduled an appointment within 13 months of their last visit.
The first time I ran the report I used this criteria:
if {APPT.APPT_DATE} >= dateadd("m", -13, currentdate)
then " "
else "PAST DUE"
What I also did was group on Patient_ID and sort by Appt_Date. I then copied both fields to the group footer and then hid the detail. That will produce a listing of the appointment with the greatest date.
When I run the report February 1st, I want another list of patients that are 13 months overdue. This group of patients should be entirely different than the patients that were on the list the previous month. I want the older than 14 months (last month's report) to roll off the report. Each month the report is run would contain different patients for 13 months overdue only.
Here is an example:
| 21001 |
1/21/2004 |
| 21001 |
4/15/2005 |
| 21001 |
12/6/2006 |
| 21003 |
5/10/2005 |
| 21003 |
6/18/2006 |
| 21003 |
11/15/2006 |
The January report will show patient number 21003 11/15/2006 as being 13 months past due.
The February report will show patient number 21001 12/6/2006 as being 13 months past due. Patient 21003 11/15/2006 should not show on the February report because it's now 14 months past due.
This is the 13 month rolling date selection criteria is need. How do I do a select "range" for 13 months only?
Thanks for your assistance.
"Pete"
|
Replies:
Posted By: Lugh
Date Posted: 08 Jan 2008 at 11:50am
You're 90% of the way there already.
Try:
IF {Appt.ApptDate} IN DateAdd("m", -14, CurrentDate) TO DateAdd("m", -13, CurrentDate)
THEN "PAST DUE"
ELSE " "
|
Posted By: PAlexander
Date Posted: 08 Jan 2008 at 1:22pm
|
I thought it had to be something like that. I went down that path originally but I couldn't get the syntax correct for some reason.
There is a concern though about using that formula. If I run the report today (01/08/2008), it will only pick up appointments between 11/08/2006 and 12/08/2006 as past due. What I really want is any past due for the entire month of November, 2006. When I run the report in February I want all of December, 2006. How would I write that formula?
The formula you suggested will work if I schedule the report to run every month on the first of the month so I can live with it. That is unless there is another solution.
Thanks for your time.
"Pete"
|
Posted By: Lugh
Date Posted: 09 Jan 2008 at 5:00am
Sure there is.
If Month({Appt.ApptDate}) = Month(CurrentDate) - 1
AND Year({Appt.ApptDate}) = Year(CurrentDate) - 1
Then "PAST DUE"
Else ""
You can't simply subtract 13, because that will just give you a negative number. But, subtracting one month and one year gives you the same result. Oh, dang, except for January. So, wrap the above in a test for January, like so:
IF Month({CurrentDate}) = 1 THEN
IF Month({Appt.ApptDate}) = 12
AND Year({Appt.ApptDate}) = Year(CurrentDate) - 2
THEN "PAST DUE"
ELSE ""
ELSE
IF Month({Appt.ApptDate}) = Month(CurrentDate) - 1
AND Year({Appt.ApptDate}) = Year(CurrentDate) - 1
THEN "PAST DUE"
ELSE ""
|
Posted By: PAlexander
Date Posted: 09 Jan 2008 at 6:14am
|
I made one small correction:({CurrentDate}) should be (CurrentDate). Other than that, it works just fine to pull out all the December, 2006 appointments.
There is still a problem. With your formula, how do I check to see if the patient has indeed scheduled an appointment? Patient 1234 had an appointment on 12/27/2006 and has a scheduled appointment on 01/21/2008. Patient should not be on the report because they have a future appointment. Wouldn't I have to do that date check first before your formula?
"Pete"
|
Posted By: Lugh
Date Posted: 09 Jan 2008 at 10:27am
Well, the ApptDate you're checking is the latest one, right? Which means there shouldn't be a later one. Unless you are storing future appointment dates in a separate field for some reason.
|
Posted By: PAlexander
Date Posted: 09 Jan 2008 at 12:32pm
|
The appointment date file can have future appointments in it. Here are test formulas I currently have set up just as checks and balances as to what exactly is in the database.
Formula: AppDate
// Appointments older than 13 months. We don’t care about these.
if {APPT.APPT_DATE} >= dateadd("m", -13, currentdate)
then " "
else "Old"
Formula: Range
//Appointments that are less than 13 months old.
if {@ApptDate} = " " and {@Future} = " "
THEN
IF Month(CurrentDate) = 1 THEN
IF Month({APPT.APPT_DATE}) = 12
AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 2
THEN " "
ELSE "Within Range"
Formula: Future
//Future scheduled appointments
if {APPT.APPT_DATE} > currentdate
then "Future"
else " "
Formula: PastDue
//Appointments older than 13 months that do not have a scheduled appointment.
if {@ApptDate} = " " and {@Future} = " "
then
IF Month(CurrentDate) = 1 THEN
IF Month({APPT.APPT_DATE}) = 12
AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 2
THEN "PAST DUE"
ELSE ""
ELSE
IF Month({APPT.APPT_DATE}) = Month(CurrentDate) - 1
AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 1
THEN "PAST DUE"
ELSE ""
Sample Data:
|
Patient |
Date |
|
1 |
11/21/2005 |
|
1 |
12/27/2006 |
|
1 |
1/21/2008 |
|
2 |
8/18/2006 |
|
3 |
1/19/2005 |
|
3 |
10/29/2007 |
|
4 |
12/16/2002 |
|
4 |
12/13/2005 |
|
4 |
12/13/2006 |
Sample Report:
|
PH |
Patient |
Date |
|
|
D |
1 |
11/21/2005 |
|
|
D |
1 |
12/27/2006 |
|
|
D |
1 |
1/21/2008 |
|
|
GF1 |
1 |
1/21/2008 |
Future |
|
D |
2 |
8/18/2006 |
|
|
GF1 |
2 |
8/18/2006 |
Old |
|
D |
3 |
1/19/2005 |
|
|
D |
3 |
10/29/2007 |
|
|
GF1 |
3 |
10/29/2007 |
Within Range |
|
D |
4 |
12/16/2002 |
|
|
D |
4 |
12/13/2005 |
|
|
D |
4 |
12/13/2006 |
|
|
GF1 |
4 |
12/13/2006 |
PAST DUE |
I hide the detail. This seems to work until I try and add a selection formula of:
@PastDue = "PAST DUE". I then get the following results:
| PH |
Patient |
Date |
|
| D |
1 |
12/27/2006 |
|
| GF1 |
1 |
12/27/2006 |
PAST DUE |
| D |
4 |
12/13/2006 |
|
| GF1 |
4 |
12/13/2006 |
PAST DUE |
Patient 1 is wrong. 12/27/2006 should not be on the report as it has a future appointment.
I'm confused and I am sure you are by now too.
"Pete"
|
Posted By: Lugh
Date Posted: 10 Jan 2008 at 5:35am
Ah. The basic problem here is one of timing. Your selection criteria are being evaluated before @Future is. So, your @Future value is actually returning the results for the previous record.
The simplest solution is to simply combine the functionality of the two formulas. So, it might look like:
IF {APPT.APPT_DATE} >= dateadd("m", -13, CurrentDate) AND {APPT.APPT_DATE} <= CurrentDate
THEN
IF Month(CurrentDate) = 1 THEN
IF Month({APPT.APPT_DATE}) = 12
AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 2
THEN "PAST DUE"
ELSE ""
ELSE
IF Month({APPT.APPT_DATE}) = Month(CurrentDate) - 1
AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 1
THEN "PAST DUE"
ELSE "" ELSE ""
Although, honestly, I'm starting to question your whole methodology. I think that you are starting to pile up too many discrete functions, and their interactions are no longer entirely clear. Also, the differences between a straight DateDiff on CurrentDate and the Month and Year comparison for @PastDue is, I think, creating some overlap. Let's try a different approach.
In each record, let's put a formula, call it @ApptStatus that looks like:
Local DateVar CalcDate := CurrentDate - Day(CurrentDate) + 1;
//This function returns the first day of the current month.
SELECT DateDiff("m",{Appt.ApptDate},CalcDate)
CASE Is > 13 : 1 //Equivalent to "Old"
CASE 13 : 2 //Equivalent to "Past Due"
CASE Is < 1 : 4 //Equivalent to "Future"
DEFAULT : 3 //Equivalent to "Current";
Now, in the group footer, we put another formula, call it @ApptDesc, that looks like:
("Old","PAST DUE","Current","Future")[Maximum(@ApptStatus,{Appt.PatientID})]
//This will return the string corresponding to the latest appointment status.
In the suppression criteria of your group footer, you can put a formula like:
Maximum(@ApptStatus,{Appt.PatientID}) <> 2
As another note, I would simply add to your Select Criteria a restriction where ApptDate >= DateAdd("m",-14,CurrentDate). This will prevent a lot of irrelevant data from coming in.
|
Posted By: PAlexander
Date Posted: 10 Jan 2008 at 5:45am
Timing is exactly the issue and that's what I was trying to get around. The various "buckets" for current, old, future, etc. were just my way to identify what exactly was in the database. I definitely want to clean all that stuff up as I don't really need it.
Let me start over and use your recommendations and I'll get back to you.
I really appreciate all the time you are taking with this "newbie" to solve my problem. I have learned a lot.
"Pete"
|
Posted By: PAlexander
Date Posted: 10 Jan 2008 at 10:13am
|
Thanks to you, I got the results I should. Report looks great.
Only issue I had was with the group footer selection criteria.
("Old","PAST DUE","Current","Future") doesn't seem to want to work before the Maximum command. I might have the parentheses in the wrong place. I'll work on that later.
Once again, thanks for all your help. I would not have been able to complete this report without your assistance. This was quite a learning experience for me. Thanks for your patience.
"Pete"
|
Posted By: Lugh
Date Posted: 10 Jan 2008 at 10:36am
That's because of a typo on my part. Try putting the square brackets around the list of descriptions, and the parentheses around the Maximum statement. That should do it.
|
Posted By: PAlexander
Date Posted: 10 Jan 2008 at 12:00pm
|
Nope, still doesn't seem to want to work.
"Pete"
|
|