Create Saved Search Shows Total Amount of Sales for the Last 6 months

Scenario

User wants to create a Transaction Saved Search that shows the total amount of sales created for a customer in a 6, 12, and 18 month period.

Solution

1. Navigate to Lists > Search > Saved Searches > New
2. Click Transaction
3. Search Title: Enter Title
4. Example: Total Amount of Sales Orders per Customer
5. Click Criteria tab
6. Click Standard subtab
7. Filter:
8. Note: For every required field use the drop-down menu to select it and once adjusted click Add if needed.

• Select Type
• Type: Select Invoice, Cash Sale, Credit Memo, Cash Refund
• Click Set
• Select Tax Line
• Tax Line: Click NO
• Click Set
• Select Shipping Line
• Shipping Line: Click NO
• Click Set
• Select Main Line
• Main Line: Click NO
• Click Set
• Select COGS Line
• COGS Line: Click NO
• Click Set

• Click Summary

• Click Results 

• Click Columns 
• Field:
• Note: For every required field use the drop-down menu to select it and once adjusted click Add if needed
• Select Date
• Select Type
• Select Name
• Summary Type: Group
• Select Item
• Select Amount
• Select Formula (Currency)
• Summary Type: Select Sum
• Formula: Enter CASE WHEN {trandate} between trunc(add_months({today}, -6)) AND last_day(add_months({today}, -0)) THEN {amount} ELSE 0 END
• Summary Label: Enter Last 6 months
• Click Save

Results