Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009

    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?

    Many thanks in advance!

    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. #2
    Join Date
    Dec 2004
    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. #3
    Join Date
    Mar 2009
    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