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]

Using the two rows you showed, your running total should have shown 1 for each row.

The {@compldate} formula is a detail level formula. I don't know what you mean by whether Group #4 is positioned correctly or why you say it is used for the compldate formula.

-LB

 

[johnwolf]

OK, I understand that given my groups the running total works as it should.

You told me to check my groups. Group#4 (customer ID) cannot be moved to any level (#1, #2 or #3) because the output would not be what I am looking for. It cannot be removed either because the "evaluate on" formula uses customer ID as an argument in the maximum() function and gives an error if the group is removed. --sorry it was not the {@compldate} formula--

The running total is in the group#3 footer. What else should I check?

Anyway questions are the following:

1. Are the data details and the example of output I described in my last post clear enough to explain what I am looking for?

2. If so, is there any way to make it ? As I wrote above, I could have a record selection selecting only completed calls if it can help.

Thanks again

 

[lbass]

Customer ID SHOULD be group #4--I wasn't suggesting otherwise. If the running total is working as I suggested, I do not understand what the continuing problem is. Please demonstrate with data the results you are getting and then show what you think they should be.

-LB

 

[johnwolf]

Detailed data:

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


Results with last running total or with the following formulas

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


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

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

#CUSTOMER CALLED
Call type:
PROSPECTION 1 //@CUSTOMERSCALLEDBYCALLTYPE
Call results:
QUALIFIED 1 //@CUSTOMERSCALLEDBYRESULT
UNAVAILABLE 1 //@CUSTOMERSCALLEDBYRESULT


The result I am looking for is the following

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

I do not want to show a 1 in UNAVAILABLE (groupfooter #3), because there is a call completed later for the same customer ID

Said differently, i would like that the only calls to be shown in the report would be the last completed call to each customers.

That is also, that the count of customer per call type (prospection) in this example would be the total of the count of customers per call result (unavailable+qualified).

Please keep asking if I do not make myself clear.

 

[lbass]

I am near the end of my patience here, but I think I see what you are saying. However, showing two records is not a sufficient sample data set. Please show a set that uses more then one customer, more than one call type, more than one call result, and more than just completed calls, and show it at the detail level. Then show the results you want to see for this set.

Also clarify whether you want only the most recent completed call across types or only within types.

-LB

 

[johnwolf]

CUSTOMER_ID CALL_ID COMPLETION_DATE CALL_TYPE CALL_RESULT CALL_STATUS PLANNED_CALL_DATE
615 143 17/11/2008 NEGOTIATION AGREEMENT COMPLETED 17/11/2008
4020 216 NULL NEGOTIATION NULL PENDING 18/11/2008
615 81 12/11/2008 PROSPECTION QUALIFIED COMPLETED 12/11/2008
3055 125 17/11/2008 PROSPECTION UNAVAILABLE COMPLETED 17/11/2008
3055 185 19/11/2008 PROSPECTION QUALIFIED COMPLETED 19/11/2008
615 256 NULL SALES NULL PENDING 21/11/2008
7263 261 20/11/2008 SALES SALE COMPLETED 20/11/2008
7263 195 17/11/2008 SALES UNAVAILABLE COMPLETED 17/11/2008
784 212 18/11/2008 SALES NO NEED COMPLETED 18/11/2008


CALL TYPE #CALLS COMPLETED % CALLS COMPLETED #CALLS PENDING #CUST CALLED % CUST CALLED
PROSPECTION 3 0 1
CALL RESULTS
QUALIFIED 2 66% 1 100%
UNAVAILABLE 1 33% 0 0%
CALL TYPE
NEGOTIATION 1 1 1
CALL RESULTS
AGREEMENT 1 100% 1 100%
UNAVAILABLE 0 0% 0 0%
CALL TYPE
SALES 3 1 2
CALL RESULTS
SALE 1 33% 1 50%
UNAVAILABLE 1 33% 0 0%
NO NEED 1 33% 1 50%

TOTAL 7 2 4

 

[johnwolf]

Sorry previous post was submitted before finished.

In bold are the count of pending calls ( that should have been completed).

I do appreciate your time and patience.

English is not my mother tongue and I am creating report in Spanish which is not my mother tongue either. That's why I might no be as clear as I would like to be.

Anyway your last question makes me think we are getting to the point: I need the most recent completed calls across types.

 

[lbass]

Create a SQL expression {%maxcalldt} by going into the field explorer->SQL expression->new and entering:

(
select max(A."CALL_DATE")
from "CALLS_TBL" A
where A."CALL_STATUS" = 'COMPLETED' AND
A."CUSTOMER_ID" = "CALLS_TBL"."CUSTOMER_ID"
)

Then create a formula {@maxdtpercust}:

if {CALLS_TBL.CALL_DATE} = {%maxcalldt} then 1

Insert a sum on this formula at the results group level and at the type group level.

-LB

 

[johnwolf]

Thank you very much

I tried your suggestion and I am stuck at creating the SQL expression.

I did it ten times, very carefully, but when I enter the expression below, I get two error messages. Sorry if translation is awkward.

First error message is :

"ORA-00932: Inconsistent data types: number expected and date returned"

Second error message is :
"Error at compiling SQL expression. Syntax error here"
The cursor is located beetween "select" and the max() expression.
(I have looked in the editor windows the SQL functions available and max() is not one of them. I do not know if it has got something to do, my version of Crystal Reports is 8.0)

Following is the SQL expression copied from the editor I tried to validate

(
select max(A."FECHA_REAL")
from "D01X_LLAMADAS_TBL" A
where A."SUBESTADO" = '90' AND
A."CODCLI" = "D01X_LLAMADAS_TBL"."CODCLI"
)

This is the real code with spanish names and here are the equivalent field names

"FECHA_REAL" is "CALL_DATE"
"D01X_LLAMADAS_TBL" is "CALLS_TBL"
'90' is 'COMPLETED' this is a string field
"SUBESTADO" is "CALL_STATUS"
"CODCLI" is "CUSTOMER_ID"

 

[lbass]

Why would you get two error messages? The max function should not be a problem--I know that works with Oracle regardless of version.

Your implementation looks correct to me. If the error relates to a date, then I'm wondering what datatype the CALLS_DATE field is--I assumed it was a date, not a string or number.

-LB

 

[lbass]

I don't know much about drivers, but a search on that error suggests it could be a driver issue. I think Oracle Server is the recommended driver, but I can't really help much in this area.

-LB

 

[johnwolf]

Thank you very much LB for time and patience, you did it.

The problem seems to be the connection beetween my PC and the server. As I am stubborn, I saved the SQL expression that raised those two errors last night, and first thing this morning I tried again and it gave no error.

The "FECHA_REAL" field is a date field, but strangely enough the SQL expression returns a string, that I had to transform again into date...and it worked.

Now, if you have a little bit of patience left, I have the last question related to this thread, what would be the way to have customers counted across call results and within call types?

Thanks again.

 

[lbass]

Since you are only counting the most recent completed call per customer regardless of type or result, you should be able to insert a sum on {@maxdtpercut} at the type group level. If this is not the result you are expecting, I'd have to see another sample of the results you are getting versus what you expect.

-LB

 

[johnwolf]

CUSTOMER_ID CALL_ID COMPLETION_DATE CALL_TYPE CALL_RESULT CALL_STATUS PLANNED_CALL_DATE
615 143 17/11/2008 NEGOTIATION AGREEMENT COMPLETED 17/11/2008
4020 216 NULL NEGOTIATION NULL PENDING 18/11/2008
7263 172 15/11/2008 NEGOTIATION AGREEMENT COMPLETED 15/11/2008
615 81 12/11/2008 PROSPECTION QUALIFIED COMPLETED 12/11/2008
3055 125 17/11/2008 PROSPECTION UNAVAILABLE COMPLETED 17/11/2008
3055 185 19/11/2008 PROSPECTION QUALIFIED COMPLETED 19/11/2008
275 278 20/11/2008 PROSPECTION UNAVAILABLE COMPLETED 20/11/2008
7263 122 12/11/2008 PROSPECTION QUALIFIED COMPLETED 12/11/2008
615 256 NULL SALES NULL PENDING 21/11/2008
784 212 18/11/2008 SALES NO NEED COMPLETED 18/11/2008
7263 195 17/11/2008 SALES UNAVAILABLE COMPLETED 17/11/2008
7263 261 20/11/2008 SALES SALE COMPLETED 20/11/2008


CALL TYPE #CUST CALLED % CUST CALLED #CUSTBYCALLTYPE % CUSTBYCALLTYPE
PROSPECTION 2 4
CALL RESULTS
QUALIFIED 1 50% 3 75%
UNAVAILABLE 1 50% 1 25%
CALL TYPE
NEGOTIATION 1 2
CALL RESULTS
AGREEMENT 1 100% 2 100%
UNAVAILABLE 0 0% 0 0%
CALL TYPE
SALES 2 2
CALL RESULTS
SALE 1 50% 1 50%
UNAVAILABLE 0 0% 0 0%
NO NEED 1 50% 1 50%

TOTAL 5

The first 2 columns are what I get with SQL expression and the {@maxdtpercut} formula.

Now what I am looking for is to distinct count customer IDs within call types and across results, that is if the same customer has a completed call in prospection call type and negotiation call type, I want its last prospection call counted in the corresponding result of prospection calls, and also its last negotiation call counted in the corresponding results of negotiation calls.

In this case, it will be counted twice in the report, once in prospection and once in negotiation, while with the current SQL expression and the {@maxdtpercut} formula it is counted once in negotiation.

 

[johnwolf]

I thought it and I tried the following, one SQL expression per call type

(
select max(A."FECHA_REAL")
from "D01X_LLAMADAS_TBL" A
where A."SUBESTADO" = '90' AND
[highlight][b]A."CODRESULTADO"='PROSPECCION' AND[/b][/highlight]
A."CODCLI" = "D01X_LLAMADAS_TBL"."CODCLI"
)

(
select max(A."FECHA_REAL")
from "D01X_LLAMADAS_TBL" A
where A."SUBESTADO" = '90' AND
[highlight][b]A."CODRESULTADO"='NEGOCIACION' AND[/b][/highlight]
A."CODCLI" = "D01X_LLAMADAS_TBL"."CODCLI"
)

(
select max(A."FECHA_REAL")
from "D01X_LLAMADAS_TBL" A
where A."SUBESTADO" = '90' AND
[highlight][b]A."CODRESULTADO"='VENTA' AND[/b][/highlight]
A."CODCLI" = "D01X_LLAMADAS_TBL"."CODCLI"
)

and so on...

 

[lbass]

What you need to do is to create a faux group within the SQL expression by setting the field from the alias table A to the actual field name. So instead of making separate SQL expressions per type, use one SQL expression like this:

(
select max(A."FECHA_REAL")
from "D01X_LLAMADAS_TBL" A
where A."SUBESTADO" = '90' AND
A."CODRESULTADO" = "D01X_LLAMADAS_TBL"."CODRESULTADO" AND
A."CODCLI" = "D01X_LLAMADAS_TBL"."CODCLI"
)

-LB

 

[johnwolf]

Ok thank you so much, everything is fine now. I got what I was looking for.

The only pb I have since the SQL expression was created, is that the reports takes like 10 minutes to print for a set of approx 3000 records, which is a lot. I am ckecking my connections and configurations. I did not find in FAQS how to improve this, maybe you have got the reference of one thread on this topic.

You deserve many stars.

 

[lbass]

I don't know of a way to improve the speed in this case. While ordinarily the use of SQL expressions improves speed, this kind of use, where the SQL expression substitutes for the use of a subreport, can slow a report somewhat.

-LB

 

[johnwolf]

OK.

Do you know wether the SQL expression are executed after the record selection or the other way round.

If they are executed before record selection, then it can explain why it is so slow, as the table in itself has got more than a hundred thousand lines.

And if it was so, maybe it would be interesting to apply the date selection within SQL expression?

 

[lbass]

The SQL expression accesses the database independently of the record selection formula, so you SHOULD build in the same criteria in the SQL expression.

-LB