Record Selection Formula Assistance

[BaldEagle0107]

I am trying to create a record selection formula that will identify my customers (customer table) who have not purchased anything from me for the last three years (Invoice Table).
I can select those invoices older than 3 years and get the customer names off of them but many of those have purchased since then as well.
Basically I am looking for all records where I have an invoice older than 3 years but the same customer does not have any invoices in the last three years.
Hope this makes sense, thanks in advance for any assistance.

BaldEagle

 

[ianoctdec]

Something around this:
formula to identify invoices not older that 3 years for each customer:
if InvoiceAgeInYears <= 3 then 1 else 0

make a sum on this formula for each customer, and select only customers that have the sum = 0

Dana

 

[BaldEagle0107]

Ianoctdec,

Not sure this solves the problem.

If for example customer 9384766 has an invoice older than 3 years but has none newer than three years I want to see that customer number.

If for example customer 3295846 has an invoice older than 3 years and also has one newer than three years then I don’t want to see that customer number.

The end result I would like to see is a list of customer numbers that have invoices older than 3 years but none new than 3 years.

Basically I want to see a list of customer numbers that have not purchased anything from me in the last 3 years.

 

[briangriffin]

If you're comfortable with command objects it would be something like:

select Customers.Name from Customers where CustomerNumber not in (select CustomerNumber from InvoiceTable where InvoiceDate > dateadd(y,-3,getdate()))

Otherwise put the max InvoiceDate in the footer of the customer group. Then use group selection to limit the results:

Maximum(InvoiceDate) < dateadd("yyyy", -3, currentdate)

The command option is better because it pulls in only the data you need. Group selection in Crystal is really just hiding the groups you don't want to see, but any totals you create will still include the unwanted records.

 

[PeterDimitrov]

LEFT JOIN Customers and Invoices and get the Customers without records. You will need to filter by Invoice date, which will require to use a command ( this cannot be done in Database Expert). Here is a sample command:

SELECT  .....  
FROM Customers c 
  LEFT JOIN Invoices i ON c.CustomerID = i.InvoiceID and o.InvouceDate>{?CutOfDate}
WHERE i.CistomerID IS NULL

{?CutOfDate} is a parameter , and you will need to insert the value when running the report. If you want to hardcode the last 3 years

SELECT  .....  
FROM Customers c 
  LEFT JOIN Invoices i ON c.CustomerID = i.InvoiceID and o.InvouceDate>DATEADD(yyyy,-3,GETDATE())
WHERE i.CistomerID IS NULL

If you do not want to use a command, you can create a view in your database and use it as a table.
The sample commands are for SQLServer. For other database you might need to change the syntax.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[BaldEagle0107]

PeterDimitrov

Thank you very much, this worked marvelously.

BaldEagle