topic Re: Creating an Average in QlikView

Creating an Average

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Creating an Average

jduarte12 — Tue, 07 Nov 2017 17:19:48 GMT

Hi,

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

Regards,

João Duarte

Re: Creating an Average

Colin-Albert — Tue, 07 Nov 2017 17:38:58 GMT

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?

Re: Creating an Average

greend21 — Tue, 07 Nov 2017 17:59:00 GMT

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];

Re: Creating an Average

greend21 — Tue, 07 Nov 2017 18:02:43 GMT

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

Re: Creating an Average

jduarte12 — Tue, 07 Nov 2017 18:05:10 GMT

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

Re: Creating an Average

el_aprendiz111 — Tue, 07 Nov 2017 18:32:01 GMT

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

;

Re: Creating an Average

kamielrajaram — Tue, 07 Nov 2017 19:15:41 GMT

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