Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
greend21
Creator III
Creator III

Creating an Average

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!

1 Solution

Accepted Solutions
kamielrajaram
Creator III
Creator III

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

View solution in original post

7 Replies
jduarte12
Partner
Partner

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

jduarte12
Partner
Partner

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

el_aprendiz111
Specialist
Specialist

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
Creator III
Creator III

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