Thursday, March 7, 2019

Search for Customers that do not place an order or do not have sale transactions for a specific timeframe like 90 days ago.


1.    Please navigate to Reports > Saved Searches > All Saved Searches > New

2.    Click on Customers link

3.    Set the following under Criteria Tab > Standard Subtab:

         Stage = Customer

         Formula (Text) || Formula:***copy the formula below || Formula (Text) = is || T

 

Case when ({today} - {lastorderdate} > 90) then 'T' else Case When ({today}-{lastsaledate} > 90) then 'T' else Case When (({lastsaledate} is null) and ({lastorderdate} is null)) then 'T' else 'F' end end end


 

4.    You may add the following under Results Tab > Columns Subtab:

         Date of Last Order

         Date of Last Sale

 

5.    Enter a Search Title

6.    Click Save & Run

Note: The following formula can also be used.

Example:  Search for Customers that do not place an order or do not have sale transactions for a specific timeframe like 2 years ago.

Case when to_number(to_char({today}, 'YYYY')) -                to_number(to_char( {lastorderdate}, 'YYYY')) = 2 then 1 else Case when to_number(to_char({today}, 'YYYY')) - to_number(to_char({lastsaledate}, 'YYYY')) = 2 then 1 else 0 end end 

No comments:

Post a Comment