Saved Search for Count of Activities and Messages for the Last Numberof Days

Here are the steps in creating a Customer Saved Search showing the total number of Activities (Phone Calls, Tasks, and Events) and Messages in a time frame in days.

1.    Navigate to Lists > Search > Saved Searches > New.

2.    Click Customer.

3.    Enter a Search Title.

4.    In the Criteria tab, tick the Use Expressions checkbox.

5.    Criteria tab > Standard subtab > use the following settings below: (Some columns that don't need to have any value are omitted from the table below).

Parens	Filter	Description	Parens	And/Or
((	Activity: Activity Type	Is any of Event Task, Phone Call	)	Or
(	Messages: Type	Is Email		And
	Messages: Is incoming	Is false	))	And

 

6.    In the Results tab > Sort By field, select Name from the dropdown list.

7.    Use the following settings below: (Some columns that don't need to have any value are omitted from the table below). Please note that for the other default Standard Fields you may use Maximum as the Summary Type.

Field	Summary Type	Formula	Custom Label
Name	Group
Formula (Numeric)	Maximum	COUNT( distinct CASE WHEN {activity.date} >= {today}-30 THEN {activity.internalid} END) + COUNT( distinct CASE WHEN TRUNC({today}) - {messages.messagedate} <= ## AND {messages.author} = {salesrep} THEN {messages.internalid} END)	Count of Activities (Activities + Messages) for the last ## days
Activity : Type
Activity : Internal ID

 8.    Click Save & Run.

Note: From the Formula field above, replace ## with the number of days users want to define as the time frame.