topic Re: Set Analysis for MTD in QlikView

Set Analysis for MTD

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

Hi Champs

Please assist i am busy with call center dash but i have realized that we dont have MTD data so we need to store or create table that will save day to day totals calls received so we can pull MTD total call received within.

E.g If totals calls received = 100 for Monday we need to store that value.

Monday or Date(22/01/2018) = 100 calls

Tuesday or Date(23/01/2018)= 200 calls

Then our totals MTD should be 300 calls received.

I am using below expression to count current day call received which works perfect.

=count({$<campaign_id={"service","technology"},user_group ={"TEL"}>}phone_number)

I tried below for month to date but not working.

=count({$<campaign_id={"service","technology"},user_group ={"Tel"}, DateField={">=$(=MonthStart(Max(DateField)))<=$(=Max(DateField))"}>}phone_number)

I hope it make sense

Re: Set Analysis for MTD

shiveshsingh — Tue, 30 Jan 2018 07:53:14 GMT

please try this..

A:LOAD lead_id,
campaign_id,
list_id,
closecallid,
call_date,
start_epoch,
end_epoch,
length_in_sec,
status,
phone_number,
user_group
FROM

(ooxml, embedded labels, table is Sheet1);

B:

load lead_id,
campaign_id,
list_id,
closecallid,
date(call_date,'DD/MM/YYYY') as Date,
start_epoch,
end_epoch,
length_in_sec,
status,
phone_number,
user_group

Resident A;

drop table A;

And expression in textbox

=count({<Date={">=$(=MonthStart(max(Date)))<=$(=max(Date))"}>}phone_number)

Re: Set Analysis for MTD

sunny_talwar — Tue, 30 Jan 2018 13:44:10 GMT

Is call_date your date_field?? It seems that this is a timestamp field... I suggest you to convert this into a date field using Floor function

Date(Floor(call_date)) as call_date

and then this

=Count({$<campaign_id = {"service","technology"}, user_group = {"Tel"}, call_date = {">=$(=Date(MonthStart(Max(call_date))))<=$(=Date(Max(DateField)))"}>} phone_number)

Re: Set Analysis for MTD

mandilicious — Wed, 31 Jan 2018 11:34:23 GMT

Thanks Sunny it is working

Re: Set Analysis for MTD

mandilicious — Wed, 31 Jan 2018 11:34:50 GMT

Thanks Shivesh