Creating an Average

[greend21]

Hi,

I'm trying to to create an average talk time field but as soon as I add this to the script the load will not work. Do you know what the issue is? Also, will this give me the correct results as Avg(TALK_TIME) does not.

sum(TALK_TIME)/Sum(ACDCALLS) as [Avg Talk Time],

Thanks!

[jduarte12]

Hi,

What is ACDCALLS? Maybe it can have some zero values that turn the division to impossible.

Regards,

João Duarte

[Colin-Albert]

To use aggregations like SUM in the script, you will need a Group By clause.

Can you show more of your script to include the "from" line and any grouping?

[greend21]

I did not use any grouping. So if the end result is going to show the avg talk time per ACD Call for each Agent ID then I would need to group by AGEND_ID?

Here is the entire script

PhoneStats:
LOAD AGENT_ID,
DATE AS [Call Date],
month(DATE) as [Call Month],
year(DATE) as [Call Year],
SPLIT,
STAFF_TIME,
TALK_TIME,
sum(TALK_TIME)/Sum(ACDCALLS) as [Avg Talk Time],
ACW_TIME,
HOLD_TIME,
ACDCALLS,
ABANDONS,
AUX_ALL_TIME,
AUX_IN_TIME,
AUX_OUT_TIME,
IDLE_TIME,
ACTUAL_CALLS,
pick(match(SPLIT,513,521), 'A, 'B') as BillSysCd
FROM $(vDDPath)(ooxml, embedded labels, table is $(vPhoneTab));

STORE [PhoneStats] INTO $(vQVDPath)PhoneStats.qvd (qvd);
DROP TABLE [PhoneStats];

[greend21]

This is a count of calls received. There are 0's in the data but is there a work around for that?

[jduarte12]

If necessary, you can put it into an if sentence.

[el_aprendiz111]

Hi,

PhoneStats:
LOAD AGENT_ID,
DATE AS [Call Date],
month(DATE) as [Call Month],
year(DATE) as [Call Year],
SPLIT,
STAFF_TIME,
TALK_TIME,
IF(ACDCALLS=0,0,TALK_TIME/ACDCALLS) AS [Avg Talk Time],
ACW_TIME,
HOLD_TIME,
ACDCALLS,
ABANDONS,
AUX_ALL_TIME,
AUX_IN_TIME,
AUX_OUT_TIME,
IDLE_TIME,
ACTUAL_CALLS,
pick(match(SPLIT,513,521), 'A, 'B') as BillSysCd
FROM $(vDDPath)(ooxml, embedded labels, table is $(vPhoneTab))
Group BY
DATE,
month(DATE),
year(DATE),
SPLIT,
STAFF_TIME,
TALK_TIME,
ACW_TIME,
HOLD_TIME,
ACDCALLS,
ABANDONS,
AUX_ALL_TIME,
AUX_IN_TIME,
AUX_OUT_TIME,
IDLE_TIME,
ACTUAL_CALLS

;

[kamielrajaram · Accepted solution]

Hi,

Would it not be easier to create an expression for average talk time like below

TIME(SUM(acdtime+i_acwouttime+i_auxouttime+i_acwintime+i_auxintime)/24/60/60,'hh:mm:ss')/

SUM(acwoutcalls+auxoutcalls+acdcalls+da_acdcalls+acwincalls+auxincalls)

Regards

Kamiel