sum calls who made call more than 5 in 2 weeks.

[manojin123]

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

[manojin123]

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

[manojin123]

Anyone have any thought on this. issue.

[avinashelite]

this should be based on the today's date or which ever the date they user picks 

[manojin123]

This should be based on today's date

[arulsettu · Accepted solution]

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)

[manojin123]

Thank you it's worked