Consecutive Count of records with criteria to allow a skip count 1

[beth4530]

I have to identify clients with 3 consecutive appointments. The client had to have shown (Status="SH") or completed (status="CO")the appointment for it to count. The tricky part is I need to skip the count for appointments that were canceled(status="CA") because of any reason other than a late cancellation (cancellation_reason ="LC"). Below is my current formula.


(Previous ({Patient_Clin_Tran.patient_id})= {Patient_Clin_Tran.patient_id}
and
({Patient_Clin_Tran.status} in [ "CO" ,"SH"] and
previous({Patient_Clin_Tran.status})in [ "CO" ,"SH"] and

next({Patient_Clin_Tran.patient_id})= {Patient_Clin_Tran.patient_id} and
next({Patient_Clin_Tran.status})in [ "CO" ,"SH"] )) = true

 

[lbass]

I would insert a group on patient ID, and sort on date and then use a formula like this to count (placed in the detail section):

//{@detail formula}:
whileprintingrecords;
numbervar cnt;
numbervar metcnt;
if {Patient_Clin_Tran.status} in ["CO" ,"SH"] then
cnt := cnt + 1 else
if {Patient_Clin_Tran.status} = "CA" and
{Patient_Clin_Cancellation_Reason" <> "LC" then
cnt := cnt else
cnt := 0;
if cnt >= 3 then
metcnt := metcnt + 1;
cnt

Add a reset in the group header:
//{@reset}:
whileprintingrecords;
numbervar cnt;
numbervar metcnt;
if not inrepeatedgroupheader then (
cnt := 0;
metcnt := 0
);

To indicate whether a client has at least one set three consecutive appointments, use a formula like this in the group footer section:

whileprintingrecords;
numbervar metcnt;
metcnt > 0

-LB

 

[lbass]

Actually the group footer formula should be:

//{@display}:
whileprintingrecords;
numbervar metcnt;
if metcnt > 0 then
"Three or More Consecutive Appointments"

-LB

 

[beth4530]

Thank you ! It worked

 

[beth4530]

How would I count the clients that have a metcnt >0??

I have suppressed records.

 

[lbass]

Change the display formula to:

whileprintingrecords;
numbervar metcnt;
numbervar metcntcusts := metcntcusts + 1;
if metcnt > 0 then
"Three or More Consecutive Appointments"

Add a display formula for the report footer:
whileprintingrecords;
numbervar metcntcusts;

If you are trying to count the customers within some higher order group, the display would be in that group footer and you would need a reset in the higher order group header:

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

-LB