How to count only the last calls made to each customer? 1

[johnwolf]

Using CR8 with Oracle

I am currently counting calls to customers in our call center, using a table which is as following

CALLS_TBL

CALL_ID
AGENT_ID
CUSTOMER_ID
CALL_TYPE (PROSPECT, NEGOTIATE, SELL...)
CALL_RESULT (UNAVALAIBLE, QUALIFIED, SALE,...)
CALL_STATUS (COMPLETED, PENDING)
CALL_DATE

On a given period established with parameters and record selection formula.

Groups are the following:
Group#1 AGENT_ID
Group#2 CALL_TYPE
Group#3 CALL_RESULT

To count customers called I used the following formulas


@CUSTOMERIDCALLS
If {CALLS_TBL.CALL_STATUS}='COMPLETED' then {CALLS_TBL.CUSTOMER_ID} else {@null}


Then I use
@CUSTOMERSCALLEDBYRESULT
DistinctCount({@CUSTOMERIDCALLS},{CALLS_TBL.CALL_RESULT})

@CUSTOMERSCALLEDBYCALLTYPE
DistinctCount({@CUSTOMERIDCALLS},{CALLS_TBL.CALL_TYPE})

@CUSTOMERSCALLEDBYAGENT
DistinctCount({@CUSTOMERIDCALLS},{CALLS_TBL.AGENT_ID})


So far so good.


So with a set of data like

CUSTOMER_ID CALL_ID DATE CALL_TYPE CALL RESULT
1 100 11112008 P UNAVAILABLE
1 165 17112008 P UNAVAILABLE
1 236 19112008 P QUALIFIED
2 126 12112008 P QUALIFIED
3 137 13112008 P UNAVAILABLE

The report looks like

GH#1 COUNT
AGENT 1 3 //@CUSTOMERSCALLEDBYAGENT

GH#2
P (PROSPECTION) 3 //@CUSTOMERSCALLEDBYCALLTYPE

GH#3
UNAVAILABLE 2 //@CUSTOMERSCALLEDBYRESULT
QUALIFIED 2 //@CUSTOMERSCALLEDBYRESULT

What I would like to count is only the latest CALL_RESULT in GH#3

so that it looks like

GH#3
UNAVAILABLE 1
QUALIFIED 2

Any suggestion welcome, do not hesitate to ask for more details if I am not clear enough

 

[lbass]

I would insert a Group #4 on Customer ID, and then go to report->selection formula->GROUP and enter:

{table.date} = Maximum({table.date},{table.customerID})

Then use a running total that does a distinctcount of {@CustomerIDCalls}, evaluate for each record, reset on change of group: {CALLS_TBL.CALL_RESULT}. Place this in the Group #3 FOOTER (not header) and suppress the GH#3.

-LB

 

[johnwolf]

Thank you very much for your help.

I tried twice and get the same exact result with the new running total as with the formula @CUSTOMERSCALLEDBYRESULT.

For some reason it seems that the group selection formula does not have any effect.

Maybe I am missing something.

Nevertheless it gave me ideas, so I created a subreport with the same record selection formula as main report.

I created a shared variable in the sub report calculating maximum({CALLS_TBL.CALL_DATE},{CALLS_TBL.CUSTOMER_ID}).

I passed this variable into the main report with the name of the formula @MAXDATE and put it in detail secion a so that detail section b where @CUSTOMERIDCALLS is, can use it.

So for each customer I get the date of the last call.

Then I tried a few things, first changing the formula as follows:

@CUSTOMERIDCALLS
If {CALLS_TBL.CALL_STATUS}='COMPLETED'AND {CALLS_TBL.CALL_DATE}=@MAXDATE then {CALLS_TBL.CUSTOMER_ID} else {@null}

This formula works correctly but all distinctcount formulas (@CUSTOMERSCALLEDBYRESULT, @CUSTOMERSCALLEDBYCALLTYPE
@CUSTOMERSCALLEDBYAGENT)strangely enough have stopped working and all give 0 as only result.

Then I tried to create a running total, but it seems impossible to create a running total based on a formula that contain a field evaluated while printing records.(@CUSTOMERIDCALLS does not appear any more in the field list for running totals).

So for the moment I do not find a solution.

 

[dgillz]

A group selection formula would not have an effect, it would count all the excluded groups. You need to use variables for this. What is your group selection formula?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[johnwolf]

Thanks,the only group selection I used is the one proposed in LB´s post.

Then related to my last post I tried another idea.
I created Group#4 on CUSTOMER_ID

I have the subreport and @maxdate formula (in GH#4)

I changed @CUSTOMERIDCALLS to
If {CALLS_TBL.CALL_STATUS}='COMPLETED'AND {CALLS_TBL.CALL_DATE}=@MAXDATE then 1 else 0

And then I built the usual 3 steps counter (init in GH#3, counter:=counter+@CUSTOMERIDCALLS in details and total in GF#3) and it works but with two major inconvenients: I cannot suppress GH#4 so printed result is awful and worst, the generation of the report is very very slow (which never has been the case with other reports including subreports and many lines).

So this last solution is not efficient for me.

 

[lbass]

I think you are making this more complicated than necessary. Please identify your current groups and explain how you set up the running total I suggested.

-LB

 

[johnwolf]

OK thanks, I hope this is the case.

I am describing the report as it is after your last post

Group#1 AGENT_ID
Group#2 CALL_TYPE
Group#3 CALL_RESULT
Group#4 CUSTOMER_ID

Nothing special for those groups except Group#3 where call results are in specific order (some of the call results are grouped for simplification)

report->selection formula->GROUP:

{CALLS_TBL.CALL_DATE} = Maximum({CALLS_TBL.CALL_DATE},{CALLS_TBL.CUSTOMER_ID})

Running total

Name:CUSTOMERCOUNT
Field to summarizeCUSTOMERIDCALLS

It is the following formula

If {CALLS_TBL.CALL_STATUS}='COMPLETED' then {CALLS_TBL.CUSTOMER_ID} else {@null}

(the @null formula is a formula with nothing in it as you showed me before)

Evaluate: for each record

Reset on: change of group: GROUP#3

I placed this running total in Group footer #3 and as I posted, it produces the same result as @CUSTOMERSCALLEDBYRESULT

DistinctCount({@CUSTOMERIDCALLS},{CALLS_TBL.CALL_RESULT})

Thanks in advance for your help

 

[lbass]

Can you explain why you are not limiting records to completed calls in your record selection formula? Also, are you looking for the most recent record of completed calls per customer ID even if there was a more recent uncompleted call? Could you please show a sample that includes the completed status and then what you would expect to see?

-LB

 

[johnwolf]

The report is showing at the same time calls completed and calls that should have been completed by current date and are still pending by call type

Then it shows calls results (that for sure only apply to completed calls). The count on customers aims to make percentage of each of the results by call type, base on # of customers and not on # of calls.

Yes I am looking for the most recent completed call ( the call date is the date of completion, there is also a plaaned call date), pending calls are shown apart.

Data sample with call status (which is C -completed- or P -pending- and nothing else) and planned call date

CUSTOMER_ID CALL_ID DATE CALL_TYPE CALL RESULT CALL_STATUS PLANNED_CALL_DATE
1 100 11112008 P UNAVAILABLE C 10112008
1 165 17112008 P UNAVAILABLE C 17112008
1 236 19112008 P QUALIFIED C 19112008
2 126 12112008 P QUALIFIED C 12112008
3 137 13112008 P UNAVAILABLE C 10112008
1 288 --- N --- P 02122008

 

[johnwolf]

Ok I did not get to attach a sample so I put you the display of the data, in bold is what I am looking for, the rest is working fine

# COMPL.CALLS %CALLS #PENDING CALLS #CUST_CALLED %CUST_CALLED

Agent:
RAFAEL

Call type:
PROSPECTION 261 100% 24 161 100%
Call results:
QUALIFIED 43 16% 36 22%
UNAVAILABLE 177 68% 89 55%
WRONG NUMBER 10 4% 10 6%
NO INTEREST 26 10% 26 16%
OTHER 5 2% 0 0%

Call type:
NEGOTIATION 358 100% 53
Call results:
AGREEMENT 52 15%
NEED NEW OFFER 35 10%
UNAVAILABLE 184 51%
.......


Hope it is clear

 

[lbass]

Remove the group selection that I suggested earlier and create a formula {@compldate} in the field explorer:

if {CALLS_TBL.CALL_STATUS}='COMPLETED' then {CALLS_TBL.CALL_DATE}

Then try a running total {#custs} that does a distinctcount of customer ID, with an evaluation formula of:

{CALLS_TBL.CALL_STATUS} = 'COMPLETED' and
{CALLS_TBL.CALL_DATE} = Maximum({@compldate},{CALLS_TBL.CUSTOMER_ID})

Reset on change of group: {CALLS_TBL.CALL_RESULT}.

This should be accurate in the result group footer. Then create a formula:

{#custs} % distinctcount({{CALLS_TBL.CUSTOMER_ID},{CALLS_TBL.CALL_RESULT})

-LB

 

[johnwolf]

Thanks LB.

I tried your last suggestion exactly as you stated.
The result of the count that is made is the same as the formula in the first post (@CUSTOMERSCALLEDBYRESULT //DistinctCount({@CUSTOMERIDCALLS},{CALLS_TBL.CALL_RESULT})), and also the same as your first suggestion.

The way I understand this is that in both cases, the maximum function on call date field is evaluated within group #3 (each group of call results) and not on the whole selection of records.

As you asked me if I needed pending calls, would it be easier if I selected only completed calls?

 

[lbass]

Please show me exactly what you did, as I don't see how you could get the same results as earlier. Explain where you put the formula (what section), too.

-LB

 

[johnwolf]

I removed the group formula selection.(Just as an information if it can help you figure out what is going wrong with my report, the results of you first suggestion remain the same wether or not there is the group formula selection)

I created a new formula {@compldate}
//if {CALLS_TBL.CALL_STATUS}='COMPLETED' then {CALLS_TBL.CALL_DATE}
that I kept in the formula field without placing it anywhere in the report.

I created the RT #custs
doing a distinctcount on {CALLS_TBL.CUSTOMER_ID}
evaluate on formula {CALLS_TBL.CALL_STATUS} = 'COMPLETED' and
{CALLS_TBL.CALL_DATE} = Maximum({@compldate},{CALLS_TBL.CUSTOMER_ID})

Reset on group#3

I put this RT in GF#3

I checked every of these steps twice to make sure that the information I send you is correct.

In order to make antoher check, I printed out the detail section and group 3 sections for a set of 100 customers and made a manual count. Customer IDs where date calls and results are different are counted for each call result.

Also, as I asked you, do you think it would be easier if I selected only completed calls ?

Thanks for time and patience.

 

[lbass]

What does this mean:

Customer IDs where date calls and results are different are counted for each call result.

Are you saying the running total was incorrect? Or the percentage? Did you use the percentage formula I suggested? I believe I suggested earlier that it would be easier if you selected only completed calls, but you seemed to need the other ones in the report.

-LB

 

[johnwolf]

It means that the same customer id has one call on 11/17 with result unavailable, and one call on 11/19 with result qualified. For this example I made a selection on one customer ID and report is like -- just showing the count of customers



#CUSTOMER CALLED
Call type:
PROSPECTION 1
Call results:
QUALIFIED 1
UNAVAILABLE 1

So as I wrote, the running total gave the same result as the formula in the first post.

What I would like to get is
Call type:
PROSPECTION 1
Call results:
QUALIFIED 1
UNAVAILABLE 0 (because this call has a completion date inferior to the one above)



For the pending calls, I think I could get them through a subreport without too many headache, so I was wondering if it was easier to have a record selection selecting only completed calls.

Thanks again LB

 

[lbass]

I cannot tell from your example whether the running total is incorrect or not, since you are not showing the detail records with the dates and status.

-LB

 

[johnwolf]

Record details of the example shown above are:

CUSTOMER_ID CALL_ID COMPLETION_DATE CALL_TYPE CALL RESULT CALL_STATUS PLANNED_CALL_DATE
3055 125 17112008 PROSPECTION UNAVAILABLE COMPLETED 17112008
3055 185 19112008 PROSPECTION QUALIFIED COMPLETED 19112008

 

[lbass]

Better check your groups. If Group #3 is call_result and Group #4 is customer ID, the result would have been 1 and 1. Make sure the running total is the Group #3 footer, NOT the header also.

Also, is your date a string? If so, I would convert that to a date before using it in any formulas.

-LB

 

[johnwolf]

That is correct, group #4 is customer ID and I do not understand what you mean by "result would have been 1 and 1".

Anyway, is group #4 correctly positionned and where should I put it? (as it is used for {@compldate} formula).

My dates are datetime variables, sorting ang comparing work normally.