Last Consecutive Count

[ramblinrose]

I use Crystal XIR2 and this is an access database.

I have clients that receive child care. I need to know the total months child care is received and the total consecutive months child care is received from the last date received by Parent SSN.

My report looks like this so far
Group 1 - Parent SSN
Group 2 - PeriodEndDate - (for each month)
Details (suppressed)
Group 1 Total months child care received is a running total of PeriodEndDate, distinctcount, evaluate on change of group 2, reset on group 1 (this works correctly)

I don't know how to get the consecutive months child care is received from the last date. The data can look like this

PeriodEndDate
04/15/2007
05/01/2007
05/25/2007
06/10/2007
07/10/2007
11/15/2008
11/30/2008
12/01/2009
12/01/2009
01/01/2010

So the end result I want is for the last record 01/01/2010 with a consecutive distinct count of months received of 3.

Thank you in advance.

 

[lbass]

Try something like this formula to be placed in GH2 and suppressed:

whileprintingrecords;
datevar currdt;
datevar prevdt := currdt;
currdt := {table.periodenddate};
numbervar mos;
if month(currdt) = month(dateadd("m",1,prevdt)) then
mos := mos + 1 else
mos := 1;

Then use a display formula in GF#1:

whileprintingrecords;
numbervar mos;

-LB

 

[ramblinrose]

WOW...you are the man.

I just wish I had more of your knowledge. Thank you so much this worked.

 

[ramblinrose]

Whoops...I think I need a reset.
This was is a result I got.

Group ParentSSN xxx-xx-1234 12 consecutive counts
Group ParentSSN xxx-xx-5678 2 consecutive counts but displays 14.

The reason the second group displays 14 is because the first record in that group is a valid consecutive date from the last record in the first group. So wouldn't I need something to reset the count at the beginning of each ParentSSN group?

 

[lbass]

Yes. To group header #1, add this formula:

whileprintingrecords;
numbervar mos;
if not inrepeatedgroupheader then
mos := 0;

-LB

 

[ramblinrose]

Thank you. Would you explain how this works?

 

[lbass]

In the first formula, prevdt is set to the currdt (current date), but this is done BEFORE the currdt is set, so therefore prevdt will reflect the last date in the previous group. This allows you to compare the months of the two dates, by seeing if adding one month to the previous date and then checking its month returns a value equal to the month of the date in the current group. If it is the same, then the months are consecutive, so the current value of mos accumulates by 1, otherwise it is set back to one. The reset formula just returns mos to 0 whenever the group header #1 executes--except if you have it set up to repeat on each page.

-LB

 

[lbass]

PS. By "current date" in my last post, I really meant the date evaluated in the current group.

-LB

 

[ramblinrose]

The requestor would now like me to include the date breaks for the last consecutive run. So in the example above, I would need to show the date 7/10/2007 and 11/15/2008. If there were additional runs and breaks for the same client, the report would only need to display the most recent break in services.

 

[lbass]

Can you clarify whether you need to see the last date in the month and the first date in the next month where there is a lapse, or whether it is sufficient to show the months, e.g., July - November.

-LB

 

[ramblinrose]

I just spoke to the end user, and you know how that is. They are never sure of what they want. The month and the year will be fine if it is easier that way.

Thank you again lbass.

 

[lbass]

Change the mos formula to:

whileprintingrecords;
datevar currdt;
datevar prevdt := currdt;
currdt := {table.periodenddate};
numbervar mos;
datevar stlapse;
datevar endlapse;
if month(currdt) = month(dateadd("m",1,prevdt)) then
mos := mos + 1 else
mos := 1;
if month(currdt) <> month(dateadd("m",1,prevdt)) then (
stlapse := prevdt;
endlapse := currdt
);
mos

Then in the group #1 footer, use a formula like this:

whileprintingrecords;
datevar stlapse;
datevar endlapse;
totext(stlapse,"MM yyyy") + " to " +totext(endlapse, "MM yyyy")

Please do NOT format to show the actual dates, as the stlapse date will pick up the first date in the month where the lapse begins, NOT the last date, since the formula is in the group header.

-LB

 

[ramblinrose]

Thank you again LB. I did that what you gave me and added a little to it to display what I needed. It sure was a help. You are a genius.