# Thread: Date Range A not in Date Range B

1. Registered User
Join Date
Mar 2009
Posts
4

## Unanswered: Date Range A not in Date Range B

I need to have a report show a "new business user"
A new business user is identified where :
a record was ordered by them in current month(passed in via parameter )
and was not in the 12 months leading up to the current month

Report is to be run once a month.

How or can this be done with crystal reports?

Current Month = 03/2009

OrderUser Date
a 05/30/2008
a 03/16/2009
b 03/12/2009
c 02/12/2009

User 'b' is a new user

2. Registered User
Join Date
Dec 2004
Location
Connecticut
Posts
85
Once you have your list of everyone who placed an order in the current month, you want to compare it to the date of their last order, if they have one. CR includes a Previous() function that lets you compare the value in one record of the report to the record before it. You also have to deal with people who only have one order (they won't have a previous record for comparison).

First you need to list all the orders for people who placed an order in the current month. I'd probably do this with a subform. The main form will list everyone who has an order during the current month. The sub form will list all the orders for that person sorted by the order_date. In the subform, you can calculate whether they qualify as a new business user

It's been a while, so my syntax is probably off, but the calculated field will look something like this:

if Month({order_date}) = {parameter} and DateDiff({order_date},NZ(Previous({order_date}),#1/1/1990#),M)>12
else ""

"Month({order_date}) gives a numeric value of the month of the order. The "NZ(Previous({order_date}),#1/1/1990#)" section substitutes a very old date for people with no previous orders.

When I'm building a complex calcualtion, I build it up one step at a time, to make sure my syntax is correct.

Once you get the subreport to return the correct value, you can then adjust the size and position of the subform so that the results of the calculated field gets displayed as part of the record in the main field.

3. Registered User
Join Date
Mar 2009
Posts
4
Thanks so much!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•