Formular for specific calculation

[3dthies]

I have created a customer report where i have all the orders for. here an example:

GROUP HEADER 1:

the customer number
44044

in the detail section:
customer customer name order number order date
44044 moiner 22 12.03.2006
44044 moiner 48 10.08.2006
44044 moiner 103 11.08.2007
...and so on

the problem i have is to display only the customers who have more then a year between a an order and the following order (for example: 10.08.2006 - 11.08.2007)

 

[lbass]

Are your dates in the format dd/MM/yyyy? Do you want to show ALL orders if ANY of the orders meet this criterion? Can the comparison be between any two dates or only that last two dates?

-LB

 

[3dthies]

My dates are in the format dd/mm/yyyy.

The comparison should be between any dates.

I think I have to work what I need.

The goal is to have a report which shows me the new customers for a specifi date range. So, that definition of a "new customer" is very easy. I have created a report which shows me all new customers for the year 2007 grouped monthly. Because in the database I have the field "creating date". So If I put in a new customer today in my applikation then I have the date 07/01/2007 then the field "creating date" and that is what I'm using for this report.

The second definition of a "new customers" is that I have also old customers who havn't placed orders for a specific time. In my case one year. For this issue I have created a second report but stucks with the specific formular. and that was the reoson for my first post

 

[lbass]

You have to allow old order dates into your report, but you could define the period under consideration by limiting records by the end or the period. So let's say you wanted to look at new customers as of 9/30/07, where new customers are those who either were new since 7/1/07 or they hadn't placed an order in the year prior to their most recent order. You would then use a record selection formula like this:

{order.date} < date(2007,10,1)

Insert a group on {table.customer} and then go to report->selection formula->GROUP and enter:

{table.createddate} >= date(2007,7,1) or
(
maximum({order.date},{table.customer}) >= date(2007,7,1) and
datediff("d",nthlargest(2,{order.date},{table.customer}),nthlargest(1,{order.date},{table.customer})) > 365
)

-LB

 

[3dthies]

aaahhh, cool.

That works fine for right now. I will make some test and validation und give you a feedback