Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Champs
I have below SQL script that i am straggling to convert to qlikview scrip as (Average Handling Time (AHT))
SELECT
(SUM([talk_sec])+ SUM([dispo_sec])) / COUNT(Phonenumber) AS AHT
FROM [Campaign_].[dbo].[vicidial_agent_log]
WHERE [lead_id] is not null AND [talk_sec] >0
Please correct me cause below it is pulling incorrect data.
=(Time(avg(dispo_sec + talk_sec)
/count(phone_number),'hh:mm'))
Thanks
try this in script
Time(sum(dispo_sec + talk_sec)/count(phone_number),'hh:mm')) as AHT
WHERE len(trim([lead_id]))>0 and [talk_sec] >0
try this in script
Time(sum(dispo_sec + talk_sec)/count(phone_number),'hh:mm')) as AHT
WHERE len(trim([lead_id]))>0 and [talk_sec] >0
Hi Tim
Thanks i have few questions
1) Is my syntax correct from SQL to Qlikview?
2)Will be fine even if i haven't used WHERE [lead_id] is not null AND [talk_sec] >0?
Thanks
1) snytax is ok but you caluclated the average and then you dont Need to dvide that with you Count(phone_number) anymore
2) it will work even without that where Statement. Note that NULL values doesnt matter for average values. But 0 values will affect your average values.
Hi Tim
Please correct me.
log_Today:
LOAD "agent_log_id",
"user",
"server_ip",
"event_time",
"lead_id",
"campaign_id",
"pause_epoch",
phone_number,
"pause_sec",
"wait_epoch",
sum(dispo_sec + talk_sec) as AHT,
Time(sum(dispo_sec + talk_sec)/count(phone_number),'hh:mm') as AHT,
"wait_sec",
"talk_epoch",
"talk_sec",
"dispo_epoch",
"dispo_sec",
status,
"user_group",
comments,
"sub_status",
"dead_epoch",
"dead_sec",
processed,
uniqueid,
"pause_type";
SQL SELECT *
FROM "Campaign".dbo."_log_Today"
WHERE len(trim([lead_id]))>0 and [talk_sec] >0
It must not be a problem here but:
sum(dispo_sec + talk_sec)
isn't mandatory the same like:
SUM([talk_sec])+ SUM([dispo_sec])
and if it had correctly worked in SQL I wouldn't change it in QlikView.
- Marcus
Hi Marcus
If i may ask then which one is correct?
Thanks
It depends on the context which of the versions is more preferable.
If one of the parts of a normal addition isn't a valid number else a string or NULL the result of the calculation will be NULL. In opposite to this a function like sum() will ignore each non-numeric value. Here a simplified example to demonstrate what happens:
load sum(a + b) as F1, sum(a) + sum(b) as F2 inline [
a, b
10, 5
20, x
5, 8
];
- Marcus