Finding 3 or More Within 60 Days (rolling dates) 1

[dmt4cats]

I am using Crystal Reports 8.5 and am trying to determine when 3 or more tickets occurred within 60 days for the same customer.

My ticket information is on separate rows. I know how to tell if there are 3 or more tickets between given dates by using a simple formula such as:

Formula 1

if {table.ticket_time} in currentdate - 61 to currentdate-1
and not isnull ({table.ticket}) then 1

Formula2

sum ({@CurrentDate-1_Formula1}, {table.customer})

Then I can do a formula that counts all tickets from Formula 2 if the result is >=3.


I have also tried using previous and next functions to get the data all on one row and then adding the dates to determine if there were 3 tickets within 60 days. If I do this I can't figure out a way to identify the 1st and 3rd tickets that meet the criteria so I can display these tickets and suppress all tickets that do not meet the criteria of 3 or more within 60 days.

Thanks for any suggestions you can provide.

 

[lyanch]

The easiest way to do this would be to only bring in the data that meets the "3 in 60" criteria. You can do this with either a pasted in subselect or a SQL expression that you can use in the record selection.

You basically want to come up with something like:

(select count(*) from table where table.ticket_time in getdate()-1 to getdate() - 61 and table.customer = maintable.customer) >= 3

Then you can sort by date and show the first and last record for the group (one in header, one in footer)

Lisa

 

[dmt4cats]

I understand what you are saying to bring in only the data that meets the criteria, however, I need to bring in year to date data and out of that data select the reports that meet the criteria of "3 in 60".

Thanks,

dmt4cats

 

[lbass]

I guess it depends on what you mean by "rolling". If you want to return records where there are at least three records within 60 days of each date, then the following might work. This approach looks at the previous 60 days, since records following the date that are within 60 days would get picked up when evaluating the subsequent date, etc.

First, add your table twice to the report, linking {table.custID} to {table_1.custID}. Then insert a group on {table.custID} and a group on {table.date}. Next create a formula {@inrange}:

if {table_1.date} in {table.date}-60 to {table.date} then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@inrange},{table.date}) >= 3

Then create a running total {#inrange}. Select {table_1.date}, count, evaluate based on a formula:

{table_1.date} in {table.date}-60 to {table.date}

Reset on change of group (date).

Then go to the section expert (format->section)->details->suppress->x+2 and enter:

not({table_1.date} in {table.date}-60 to {table.date}) or
not({#inrange} = 1 or
{#inrange} = sum({@inrange},{table.tabledate}))

This should return each date group that has at least three dates within 60 days, but display only the first and last records that fall within the 60-day period.

-LB

 

[dmt4cats]

I need to identify 3 or more tickets within a 60 day timeframe. The report needs to go back further than the last 60 days. For example, from January 1, 2004 thru the current date, identify all tickets that meet the criteria of 3 or more within 60 days.

Thanks for any and all suggestions.

dmt4cats

 

[lbass]

The solution I provided above applies to any date, i.e., it displays those dates with clusters of three or more dates within 60 days of the date in any particular record. Did you try this? In order to get further assistance, it helps to explain why a particular solution does not work for you, so that responders can tailor their suggestions based on what has already been tried.

If my solution is not what you are looking for, you should explain why, since from what you've said so far, this should meet your needs. Ideally, you would supply a sample of what your data looks like and how you would like your report to look.

-LB

 

[dmt4cats]

I did get this to work with the solution provided. Now I have a another question.

With the report grouped by the custID and the date and where we are looking for 3 or more tickets within a 60 day timeframe, there are some tickets for the same custID that show up under more that one of the date groups. Is there anyway I can suppress the ticket if there is more than one for the same custID by under a different date group? As a final result my customer only wants to see each ticket one time.

I'm not sure how to use a distinct since they appear in different groups.

Thank you for the help you have provided.

 

[lbass]

The report results above showed a customer group, a ticket date group (if the date met the criterion that there were three or more tickets within 60 days), and then the first and last ticket date for those date groups with 3 or more dates. If you change the detail level suppression to:

not({table_1.date} in {table.table.date}-60 to {table.date})

...you can see all the dates that fell within a 60-day period for each ticket date. It looks something like:

Cust 1
2/4/2004
1/16/2004
2/01/2004
2/04/2004

2/14/2004
1/16/2004
2/01/2004
2/04/2004
2/14/2004

3/23/2004
2/01/2004
2/04/2004
2/14/2004
3/23/2004

Note that except for the first group in each customer group, each detail date will also appear as a date group at some point. So, to show distinct dates that meet the criterion, you only need to display the details of the first group per customer, and then display the rest of the date groups per customer. To do this, first create a running total: Select {table.date}, distinctcount, evaluate on change of group (table.date), reset on change of group(custID).

Then change the detail suppression formula to:

not({table_1.date} in {table.table.date}-60 to {table.date}) or
{#grpcnt} <> 1

Then format the {table.date} group header to suppress with:

{#grpcnt} = 1

Align the detail and group header date fields.

-LB

 

[dmt4cats]

I'm not getting the results I expect to see when I try to suppress the distinct rows of data.

Following is an example of what I have that meets the criteria of 3 in 60 days, and the way it is grouped.

Cust 1
3/6/2004
3/7/2004
4/17/2004

4/17/2004
5/15/2004
6/11/2004

I want to be able to suppress the group headers and footers, then in the detail section the results I want to see is:
3/6/2004
3/7/2004
4/17/2004
5/15/2004
6/11/2004

If the same date appears in different groups, I want to be able to suppress the duplicates and only show each row of data only once. Is a distinctcount runningtotal the correct way to go?

Thanks for all your help.

 

[lbass]

The following solution will work as long as each customer doesn't have so many distinct dates that the CR 8.5 string limit of 254 is exceeded.

First, to simplify the report, using the formula from my first post, add the following to your record selection formula:

{@inrange} <> 0

Your group selection formula should contain:

sum({@inrange},{table.date}) >= 3

Undo the suppression formula I earlier suggested. Then create three formulas (each of these can be suppressed):

//{@reset} to be placed in the CustomerID group header:
whileprintingrecords;
stringvar x := "";

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar x;

if instr(x, totext({table1.date},"M/dd/yy")) = 0 then
x := totext({table1.date},"M/dd/yy")+ ", " + x else
x := x;

//{@instr} to be placed in the details section:
whileprintingrecords;
stringvar x;

instr(x,totext({Orders_1.Order Date},"M/dd/yy"))

Then go to the section expert->details->suppress and enter:
{@instr} >= 1

You can also now suppress the group header for the date group.

-LB