Saturday, July 6, 2019

Create a Saved Search to List All Customers with Sales on the Same Month Last Year but no Sales this Month

- Create a Customer Saved Search:
 
1. Navigate to Lists > Search > Saved Searches > New.
2. Select Customer.
3. Give a name to the Search.
4. Go to the Criteria tab and check Use Expressions.
 
- Filter the transactions based on type and date range:
 
5. Go to the Criteria tab > Standard subtab and add four filters:
5.1. Filter one:
5.1.1. Filter = Transaction Fields… Main Line is true.
5.1.2. And/Or = And.
5.2. Filter two:
5.2.1. Filter = Transaction Fields… Type is any Cash Sales, Invoice.
5.2.2. And/Or = And.
5.3. Filter three:
5.3.1. First Parens column = '('.
5.3.2. Filter = Transaction Fields… Date is within same month last fiscal year.
5.3.3. And/Or = Or.
5.4. Filter four:
5.4.1. Filter = Transaction Fields… Date is within this month.
5.4.2. Second Parens column = ).

- Use formulas on the results tab to count the number of transactions for the date ranges:
 
6. Go to the Results tab > Columns subtab and add three fields:
6.1. Field one:
6.1.1. Field = ID.
6.1.2. Summary Type = Group.
6.2. Field two:
6.2.1. Field = Formula (Numeric).
6.2.2. Summary Type = Sum.
6.2.3. Formula = CASE WHEN {transaction.trandate} between trunc(add_months({today}, -12), 'month') AND last_day(add_months({today}, -12)) THEN 1 ELSE 0 END
6.2.4. Summary Label = 12 Months Ago
6.3. Field three:
6.3.1. Field = Formula (Numeric).
6.3.2. Summary Type = Sum.
6.3.3. Formula = CASE WHEN {transaction.trandate} between trunc({today}, 'month') AND last_day({today}) THEN 1 ELSE 0 END
6.3.4. Summary Label = This Month.
 
- Use formulas to filter the summary rows results based on number of transactions by date range:
 
7. Go to the Criteria tab > Summary subtab and add two lines:
7.1. Line one:
7.1.1. Summary Type = Sum.
7.1.2. Field = Formula (Numeric).
7.1.3. Formula = CASE WHEN {transaction.trandate} between trunc(add_months({today}, -12), 'month') AND last_day(add_months({today}, -12)) THEN 1 ELSE 0 END greater than 0
7.1.4. And/Or = And.
7.2. Line two:
7.2.1. Summary Type = Sum.
7.2.2. Field = Formula (Numeric).
7.2.3. Formula= CASE WHEN {transaction.trandate} between trunc({today}, 'month') AND last_day({today}) THEN 1 ELSE 0 END equal to 0
 
8. Click Save

No comments:

Post a Comment