Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
Hope you guys help me on this piece.
I need to create a master measure which show the customer call more than 5 time in 14 days .
Thanks you
May be like this
load *,
date(hatDtTm) as Date;
load *
Inline [
hatDtTm, ContactId ,CALLS
7/6/2021 12:55:31 PM ,1, 1
7/8/2021 11:13:17 AM, 2, 1
7/11/2021 2:13:48 PM, 3, 1
7/9/2021 1:39:33 PM, 1, 1
7/7/2021 12:05:02 PM, 1, 1
7/23/2021 12:56:04 PM, 3, 1
7/9/2021 3:30:30 PM, 5, 1
5/10/2021 3:30:30 PM, 1, 1
4/11/2021 3:30:30 PM, 2, 1
3/12/2021 3:30:30 PM, 5, 1
5/13/2021 3:30:30 PM, 4, 1
6/14/2021 3:30:30 PM, 3, 1
5/15/2021 3:30:30 PM, 2, 1
2/16/2021 3:30:30 PM, 1, 1
6/17/2021 3:30:30 PM, 1, 1
3/18/2021 3:30:30 PM, 3, 1
];
Count({<Date={">=$(=date(today()-14))"},ContactId={'=sum(aggr(count(CALLS),ContactId,Date))>2'}>}CALLS)
Here is the sample data. can any one help me on this dataset.
hatDtTm, ContactId ,CALLS
7/6/2021 12:55:31 PM ,1, 1
7/8/2021 11:13:17 AM 2 1
7/11/2021 2:13:48 PM 3 1
7/9/2021 1:39:33 PM 1 1
7/7/2021 12:05:02 PM 1 1
7/23/2021 12:56:04 PM 3 1
7/9/2021 3:30:30 PM 5 1
5/10/2021 3:30:30 PM 1 1
4/11/2021 3:30:30 PM 2 1
3/12/2021 3:30:30 PM 5 1
5/13/2021 3:30:30 PM 4 1
6/14/2021 3:30:30 PM 3 1
5/15/2021 3:30:30 PM 2 1
2/16/2021 3:30:30 PM 1 1
6/17/2021 3:30:30 PM 1 1
3/18/2021 3:30:30 PM 3 1
Anyone have any thought on this. issue.
this should be based on the today's date or which ever the date they user picks
This should be based on today's date
May be like this
load *,
date(hatDtTm) as Date;
load *
Inline [
hatDtTm, ContactId ,CALLS
7/6/2021 12:55:31 PM ,1, 1
7/8/2021 11:13:17 AM, 2, 1
7/11/2021 2:13:48 PM, 3, 1
7/9/2021 1:39:33 PM, 1, 1
7/7/2021 12:05:02 PM, 1, 1
7/23/2021 12:56:04 PM, 3, 1
7/9/2021 3:30:30 PM, 5, 1
5/10/2021 3:30:30 PM, 1, 1
4/11/2021 3:30:30 PM, 2, 1
3/12/2021 3:30:30 PM, 5, 1
5/13/2021 3:30:30 PM, 4, 1
6/14/2021 3:30:30 PM, 3, 1
5/15/2021 3:30:30 PM, 2, 1
2/16/2021 3:30:30 PM, 1, 1
6/17/2021 3:30:30 PM, 1, 1
3/18/2021 3:30:30 PM, 3, 1
];
Count({<Date={">=$(=date(today()-14))"},ContactId={'=sum(aggr(count(CALLS),ContactId,Date))>2'}>}CALLS)
Thank you it's worked