Saved Search to Display Customers without Sales starting a Specific Date

Customer would like to create a Saved Search for Customers that have no Sale Transaction starting a specific Date. You may include also the column for Total Sales before the Date.

 

For example: Date is May 1, 2013

 

1. Navigate to Lists> Search> Saved Searches> New> Customer.
2. Criteria tab> Summary subtab
--Sum | Formula ( Numeric) | is 0 | Formula: Case When {transaction.trandate} <= TO_DATE('05/01/2013', 'MM/DD/YYYY') then 0 else 1 end
3. Results tab> Columns subtab
--Name | Group
--Formula Date| Formula: TO_DATE('05/01/2013', 'MM/DD/YYYY')
--Formula (Numeric) | Sum | Formula: Case When {transaction.trandate} <= TO_DATE('05/01/2013', 'MM/DD/YYYY') then 0 else 1 end
--Transaction fields: Number
--Transaction fields: Date
--Transaction fields: Type
--Formula (Numeric) | Sum | Formula: Case When {transaction.accounttype} = any('Income', 'Other Income') then {transaction.amount} else 0 end | Custom Label: Total Sales during May
4.Save