Date Range Formula Question

[keefriffhard]

I'm trying to define a customer as "Reactivated" during a given month. For example, if I were to flag a customer as "Reactivated in August, 2002" and define it as one who had an order date occurrance during the month of August, but had no orders in the past six months or more from 8/1, how would I do this?
Right now I have:

if {oehist_summary.ORDER_DATE} = Date (2002,08,01) to Date (2002,08,31) and
{oehist_summary.ORDER_DATE} <> Date (2002,07,31) to Date (2002,02,01) and {oehist_summary.ORDER_DATE} < Date (2002,01,31) then &quot;Reactivated in August&quot;

which is not working. Can you help? (Using CR8)

 

[IdoMillet]

One option is to create a query (in Crystal SQL Designer or create it as a View in your DBMS) along the following lines:
---------------------------------------------
SELECT Cust_ID, Cust_Name, Order_Date, ...
WHERE Cust_OUT.Cust_ID = Order_OUT.Cust_ID
FROM CUST AS CUST_OUT, ORDER AS ORDER_OUT, ...
WHERE CUST_ID NOT IN
(SELECT CUST_ID FROM CUST AS CUST_IN, ORDER AS ORDER_IN
WHERE
Cust_IN.Cust_ID = Order_IN.Cust_ID AND
CUST_IN.Cust_ID = CUST_OUT.Cust_ID AND
ORDER_OUT.Date - ORDER_IN.Date < 183)
---------------------------------------------

This returns all customer orders where that customer didn't have orders in the previous 183 days (~6 months).

The benefit of this approach is that you are not tied to a specific month (August) and you can apply the same logic to
find &quot;Reactivation&quot; in the past (not just last month).

Then just group the report on Month of Order_Date
and you are home free... )

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Long message, so I decided to break it into parts.

Another option is to Group the report by Customer and within Customer by Order_Date. In each Order section Use the Previous() function to detect if the Previous row is for the same Customer and if the Previous Order Date is more than 6 months before the date of the Order in the current section.

hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[keefriffhard]

Thanks....the Previous () function worked with a slight tweak.

much'o'bliged