Saved Search > Show Customers Who Have Not Ordered Specific Items

To create a saved search that shows which Customers have not bought specific items in a given period, please follow the steps below:

1. Navigate to Reports > Saved Searches > All Saved Searches > New > Customer
   
   
2. Under the Criteria tab > Summary subtab >set the following:
   
   
3. Summary Type= Maximum
   1.  Field= Formula (Numeric)
      NOTE: After selecting this, a Saved Customer Search box will appear
      
      
   2. Set Formula as:
      case when {transaction.item} IN ('Item1','Item2','Item3') then case when {transaction.trandate} between to_date('2/1/2017','mm/dd/yyyy') and to_date('2/28/2017','mm/dd/yyyy') then 1 end else 0 end
      NOTES:
      - For the formula, kindly replace the Items to the specific name of the items you want to include in the search and enclose them in ' ' as shown above. Items are separated with commas.
      - For the date, you may set it to a specific calendar date range. In the formula above, it was set to filter the whole month of February 2017. 
      
      
   3. Set Formula (Numeric) as Equal to
      
      
   4. Value (a.k.a. Description) = 0
      
      
4. Under the Results tab > Columns subtab >set the following:
   1. Add the Name field with corresponding Summary Type of Group
      
      
5. Create a Search Title 
   
   
6. Click Save & Run 

The saved search will now show all customers who have not ordered the specific items indicated within a specific date range

IMPORTANT NOTES:

• Regarding the Columns that show when running the search, please limit the Fields that you set in the Results tab > Columns subtab to only Name, because it will take a long time for the search to run if we add more results.

• When setting the date filter, it is necessary to edit it within the Formula box under the Criteria tab each time a search is being done. We cannot add the usual Available Filters for this since it causes a contradiction in the search being done.

• To efficiently run the search, please limit the calendar date range to a minimum so that the SQL will not time out. The reason why it times out when a large date filter made is because the search utilizes a Maximum Summary Type for its Criteria which pulls up a lot of information from the database.