Report Formula Help

[Crystalyzer]

I am using CR XI. My data is as follows:

Company	Activity	   Date
A	   Left Voicemail	08/22/05
A	   Email Sent	    08/23/05
A	   Email Received	08/24/05
A	   Email Sent	    08/25/05
B	   Left Voicemail	08/22/05
B	   Email Sent	    08/23/05
B	   Email Sent	    08/25/05
C	   Email Sent	    08/23/05
C	   Phone Call        08/24/05
C	   Email Sent	    08/25/05
C	   Left Voicemail	08/26/05

A Left Voicemail or Email sent is considered a solicitation. A phone call or an email received is considered a response. What I need to calculate for each company is how many solicitations occured before a response was received. I don't want to include any solicitation that occurs post a response being received.

The results I am looking for are as follows:

Company A 2 Solicitations
Company B 3 Solicitations
Company C 1 Solicitation

Can someone point me in the right direction?


Thanks and best regards,
-Lloyd

 

[paulmarr]

Hey Lloyd,

You need to use a running total - count the 2nd column and use a formula in the evaluate section - something like:

{db.field} in ["Left Voicemail","Email Sent"]

and a reset formula of:
{db.field} in ["Phone Call","Email Received"]

Group your report by company and put the running total in the group footer.

Hope this helps you.

Cheers,
Paulmarr

 

[lbass]

Create two formulas:

//{@solicitation}:
if {table.field} in ["Left Voicemail","Email Sent"] then {table.date}

//{@response}:
if {table.field} in ["Phone Call","Email Received"] then {table.date}

Insert a group on company, and then go to edit selection formula->GROUP and enter:

{@solicitation} <= minimum({@response},{table.company})

Then use a running total that counts any recurring field, evaluates for each record, and resets on change of group (company). Place the running total in the group footer, and then suppress the detail section.

-LB