Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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!

Tags (3)
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Creating an Average

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
Highlighted
Partner
Partner

Re: Creating an Average

Hi,

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

Regards,

João Duarte

Highlighted

Re: Creating an Average

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?

Highlighted
Creator III
Creator III

Re: Creating an Average

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

Highlighted
Creator III
Creator III

Re: Creating an Average

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

Highlighted
Partner
Partner

Re: Creating an Average

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

Highlighted
Specialist
Specialist

Re: Creating an Average

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

;

Highlighted
Creator III
Creator III

Re: Creating an Average

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