Customer Saved Search to List All Customers with Sales on the Same Month Last Year but No Sales This Month

Scenario

User wants to create Customer Saved Search to List All Customers with Sales on the Same Month Last Year but no Sales this Month.

Solution

1. Navigate to Lists > Search > Saved Searches > New
2. Click Customer
3. Search Title: Enter Title
   Example: All Customers with Sales on the Same Month Last Year but no Sales this Month
4. Click Criteria tab
5. Click Standard subtab
6. Use Expressions: Enter Checkmark
7. Filter:
   Note: For every required field use the drop-down menu to select it and once adjusted click Set if needed.

   • Select Transaction Fields…
   1. Transaction Filter: Select Main Line
   2. Main Line: Select YES
   • And/Or: Select And
   • Select Transaction Fields…
     
     1. Transaction Filter: Select Type
     2. Type: Select any of
     3. Select Cash Sale, Invoice
   • And/Or: Select And
   • First Parents: Select (
   • Select Transaction Fields…
   1. Transaction Filter: Select Date
   2. Date: Select within
      
   3. Select same month last fiscal year
   • And/Or: Select Or
   • Select Transaction Fields…
   1. Transaction Filter: Select Date
   2. Date: Select within
      
   3. Select this month
   • Second Parents: Select )

8. Click Summary subtab
9. Field:
   Note: For every required field use the drop-down menu to select it and once adjusted click Set if needed.

   1. Summary type: Select Sum
   2. Select Formula (Numeric)
   3. Click Set Description symbol
      

      • Formula: Enter CASE WHEN {transaction.trandate} between trunc(add_months({today}, -12), 'month') AND last_day(add_months({today}, -12)) THEN 1 ELSE 0 END
      • Formula (Numeric): Select greater than
      • Value: Enter "0"

   4. Summary type: Select Sum
   5. Select Formula (Numeric)
   6. Click Set Description symbol

   • Formula: Enter CASE WHEN {transaction.trandate} between trunc({today}, 'month') AND last_day({today}) THEN 1 ELSE 0 END
   • Formula (Numeric): Select equal to
   • Value: Enter "0"

10. Click Results tab
11. Click Columns subtab
12. Field:
    Note: For every required field use the drop-down menu to select it and once adjusted click Add if needed.

    • Select: Internal ID
    • Summary Type: Select Group
    • Select: Formula (Numeric)
    • Summary Type: Select Sum
    • Formula: Enter CASE WHEN {transaction.trandate} between trunc(add_months({today}, -12), 'month') AND last_day(add_months({today}, -12)) THEN 1 ELSE 0 END
    • Summary Label: Enter 12 Months Ago
    • Select: Formula (Numeric)
    • Summary Type: Select Sum
    • Formula: Enter CASE WHEN {transaction.trandate} between trunc({today}, 'month') AND last_day({today}) THEN 1 ELSE 0 END
    • Summary Label: Enter This Month

13. Click Save & Run