how to Show customer cases created

[#1]

how to Show CRM customer cases created, only if they are more than 5 new cases for A customer in the last week compared to today's Date

So

if Cases for a customer opened/created in the 7 days compared to today's date and are more than 5 in number for that customer = Show.

Ideally a pop up to the user or an alert if the condition is met.

Thanks in anticipation!

[pover]

Hopefully your CRM system gives you a Create Date field and then you can write a simple expression like the following to count the number of create dates within the 7-day range and insert it in the conditional show field in the layout tab and in the condition field in the Tools->Alerts window.

count(if(Create_Date>=today()-7 and Create_Date < today(), Create_Date))>5

If you don't have a create date field then tell us what data you do have to see where we go from there.

Regards.

[#3]

Hi,

thanks for your response,

the create date field is CaseCreateDate

My Condition is

=count(if(CaseCreateDate>=today()-7 and CaseCreateDate< today(), CaseCreateDate))>5

as soon as i apply, the table disappears, i even removed the >5 condition, and there are cases created in the last seven days.

Not sure where i am going wrong.

Thanks

[pover · Accepted solution]

The formula looks alright so I would recommend verifying the format of CaseCreateDate to make sure it is being considered a dual data type (date and number). Just do a num(CaseCreateDate) and it should return a number corresponding to the date and not null.

After that I would copy the formula to an text object the just start taking the formula apart and test each part of it to see where the error might be.

Regards.

[#5]

Hi,

Thanks, with a couple of changes as per the script, it worked.

Now my next mission is to create pop up or email alerts if this condition is met so that the account manager is reminded to go and do something with that information.

Many thanks
Raj