Group By Date Based on Gap from Previous Record Date

[Ryan621]

I am using Crystal Reports XI (11.5) to query a CRM database on a 2008 SQL Server via ODBC.

We are attempting to learn more about our sales cycle, how many calls required by sales team, how long from the first call until the person becomes a client, things of that nature.

Here is my Select Expert:
{BROKERAGE_ACCOUNT.BROKERAGE_NUMBER} like "R*" and
{BROKERAGE_ACCOUNT.FORMIN} > CurrentDate - 180 and
//the two items above identify the record as a customer who opened and account in the last 180 days
{ACCOUNT_ADDITIONAL.PROSPECTORID} like "U*"and
//Prospector (Salesperson) field is populated
{HISTORY.DESCRIPTION} like "Connect*" and
//Phone call that resulted in a conversation
{HISTORY.COMPLETEDDATE} < {BROKERAGE_ACCOUNT.FORMIN}
//Phone call happened before the prospect became a client

Group 1 = {BROKERAGE_ACCOUNT.BROKERAGE_NUMBER}

I have Record Sort Expert set to sort Ascending on {HISTORY.COMPLETEDDATE}.

I have a formula called {@Gap} which displays the number of days between Connects. Here is that formula:
If
{BROKERAGE_ACCOUNT.BROKERAGE_NUMBER} = Previous({BROKERAGE_ACCOUNT.BROKERAGE_NUMBER})
then
{HISTORY.COMPLETEDDATE} - Previous ({HISTORY.COMPLETEDDATE}) else
0

I want to create a new Group level 2 each time {@Gap} is equal to greater than 60 days. Basically I'd like a Group 2 each time we go more than 60 days without contacting the prospect.

I've never tried to do a group like this before so I have no idea how really to go about it. Any pointers would be greatly appreciated!

 

[Madawc]

You can only group on the basis of the values in a single detail line, not something based on a comparison with previous.

No real way round, unless you process the data using an SQL Command. Or Top N might be useful, depending on what you want.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP