Set Analysis for MTD

[mandilicious]

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

[shiveshsingh · Accepted solution]

Hi

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)

[sunny_talwar]

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)

[mandilicious]

Thanks Sunny it is working

[mandilicious]

Thanks Shivesh