We would like to create a saved search that shows the total purchases a customer has made the past 90 days when they have purchased a particular item (item A).
We have a transaction saved search setup with these criteria filters:
Type: any of Cash Sale, Invoice, Credit Memo, Cash Refund
Item: Item A
Tax Line: False
Shipping Line: False
Main Line: False
COGS Line: False
The results fields are:
Name: Summary Type Group
Date Created: Summary Type Group
Item: Summary Type Group
Formula (Currency): Summary Type SUM, Formula: CASE WHEN {trandate} between trunc(add_months({today},-3)) AND last_day(add_months({today},-0)) THEN {amount} ELSE 0 END
Using this setup, it accurately displays the total amount the customer purchased with that item over the past 90 days. It does not, however, display the total amount of all items the customer purchased over the past 90 days.
What can we do so this saved search will show the total amount of all purchases they have had over the past 90 days if one of those purchases was for item A? This setup only shows total purchases for the customer over the past 90 days of item A.