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
- Navigate to Lists > Search > Saved Searches > New
- Click Transaction
- Search Title: Enter Title
Example: Total Amount of Sales Orders per Customer - Click Criteria tab
- Click Standard subtab
- Filter:
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 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
- Select Formula (Currency)
- Summary Type: Select Sum
- Formula: Enter CASE WHEN {trandate} between trunc(add_months({today}, -12)) AND last_day(add_months({today}, -0)) THEN {amount} ELSE 0 END
- Summary Label: Enter Last 12 months
- Select Formula (Currency)
- Summary Type: Select Sum
- Formula: Enter CASE WHEN {trandate} between trunc(add_months({today}, -18)) AND last_day(add_months({today}, -0)) THEN {amount} ELSE 0 END
- Summary Label: Enter Last 18 months
- Click Save