Inactive customer list formula help

[JoyCR9]

I'm trying to produce a CR9 report listing those customers that haven't ordered in the last 6 months. I need help writing a formula to do so. I've tried a few different options that don't work.
Thanks.

 

[lbass]

First, you need to define what customers you want to include, since you probably don't want to include customers who haven't ordered for many years. Let's say you want to include any customer who has ordered since January 1, 2003, but who hasn't placed an order in the last six months.

Use a record selection formula like:

{table.date} >= date(2003, 01, 31)

Insert a group on {table.customerID}.

Then go to report->edit selection formula->GROUP and enter:

maximum({table.date},{table.customerID}) < dateadd(&quot;m&quot;,
-6,currentdate)

You would then need to use running totals for calculations, since non-group selected records will still contribute to the more usual summaries.

-LB

 

[JoyCR9]

maximum({ORDER.CREATE_DT},{COMPANY.COMPANY_ID}) < dateadd(&quot;m&quot;,-6,currentdate)
gives me message:
&quot;There must be a group that matches this field&quot;.

I have both ORDER.CREATE_DT & COMPANY_COMPANY_ID as groups in my report.

Argggggg!!! What am I missing?

 

[MJRBIM]

Please post some sample data with headers, and someone will be able to help.

 

[JoyCR9]

Company_ID: 1039
Order.Create_DT: 2003/01/15, 2003/05/04, & 2003/06,06
Company_ID: 550
Order.Create_DT: 2003/01/21, 2003/05/31

I want my report to list customer id 550, and not 1039. Table Order has Order.Company_ID as well with the same company_id numbers. (my link point)

 

[lbass]

Where are you putting the formula? It should be in the group select statement. I've never seen that message before--in 8.0 if you hadn't already created a group you would get the message the &quot;summary/running total field could not be created.&quot;

The formula should work. Also, none of the dates in your example represent orders within the last six months.

-LB

 

[MJRBIM]

This will look at all your orders, then flag them if they are within the last 6 months.

Insert the following formulas into the detail section of your report...

Name: {@CurrentDateLess6Months}
Formula: IF Month (CurrentDate) < 7 THEN Date ((Year (CurrentDate)-1),(Month (CurrentDate)+6) ,(Day (CurrentDate)))
ELSE Date ((Year (CurrentDate)),(Month (CurrentDate)-6) ,(Day (CurrentDate)))


Name: {@LastSixMonthsBinaryFlag}
Formula: IF Date({Table.Order_Create_DT}) < {@CurrentDateLess6Months} THEN 0 ELSE 1

Then Group on the Company_ID and sub-total the {@LastSixMonthsBinaryFlag}. It should return data like this....

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Company_ID Order_CreateDate CurrentDateLess6Months LastSixMonthsBinaryFlag
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

550 1/21/2003 07/13/2003 0.00
550 5/31/2003 07/13/2003 0.00
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Orders Within Period 0.00


1039 1/15/2003 07/13/2003 0.00
1039 5/04/2003 07/13/2003 0.00
1039 6/06/2003 07/13/2003 0.00
1039 1/30/2003 07/13/2003 1.00
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Orders Within Period 1.00
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


You can then supress sections using the Section Expert where -

Sum ({@LastSixMonthsBinaryFlag}, {Table.Company_ID}) > 0

Hope it helps.

 

[vidru]

Here's another potential solution:

1) Main report table -> Company

2) Create a SQL Expression field called MaxOrderDate with the following formula (including the parentheses):

(SELECT MAX(Create_DT) FROM ORDERS WHERE Company_ID = COMPANY."COMPANY_ID")

** The SQL may differ depending on your db type, but it should work for most.

3) Enter a Record Selection Formula like this:

{%MaxOrderDate} < dateadd(&quot;m&quot;, -6, currentdate)

4) On the report, drag Company_ID and %MaxOrderDate into the details section, and you should see your list of customers who didn't send an order in the last 6 months, and the date of their last order.

-dave

 

[JoyCR9]

Thanks everyone, I will test out my options in a little bit.

 

[JoyCR9]

Thanks everyone, the last 2 suggestions worked!