Min Date Value by Account

[tsouth4]

I inherited a report which I need to make a revision and would like to do so without having to rewrite it.

The report is currently grouped by customer segment and within segment each customer can have multiple orders. Here is an example of what the data may look like.

Segment|Email|Order_Date|Orderid
Sorority,test@email.com,10/01/2012,10001
Sorority,test@email.com,10/10/2012,10410
Sorority,test2@email.com,10/07/2012,10311
Sorority,test@email.com,10/05/2012,10200
Sorority,test3@email.com,10/15/2012,10425
Sorority,test3@email.com,10/19/2012,10630

I need to determine the min order date for each customer in order to tell whether they were new or a repeat. Each customers first order within the date range should always be new and each subsequent order for that customer would be repeat. Perhaps there is an easy way of doing this without having to create a subreport or rewrite in sql.

I would like my data to appear as follows.

Segment|Email|Order_Date|Orderid|new_repeat
Sorority,test@email.com,10/01/2012,10001,new
Sorority,test@email.com,10/10/2012,10410, repeat
Sorority,test2@email.com,10/07/2012,10311,new
Sorority,test@email.com,10/05/2012,10200,repeat
Sorority,test3@email.com,10/15/2012,10425,repeat
Sorority,test3@email.com,10/19/2012,10630,new

Thanks for your help

 

[IanWaterman]

Sort records by email and order date ascending.

create formula

@New
If onfirstrecord then 'New'
else
if email <> previous(email) then 'New' else 'Repeat'

Ian

 

[tsouth4]

Yes, that will work but unfortunately the user wants it sorted by date placed and not account. I know that i could easily do this in sql but it would require me to rebuild the report and write the query. Just trying to find an easier solution.

 

[IanWaterman]

The only other possibility I can think of is to use a Dynamic array.

You collect the emails in an array then using a formula to test whether email exists in array. (Before adding current email address to the Array) ;-)

But this would limit you report to 1000 email addresses as that is the max size of an array.

Ian

 

[tsouth4]

I thought about that but I would prefer to refrain from using an array? I'll have to do some more thinking. I could create a shared variable in a subreport but this report is already a little slow.

 

[lbass]

You could create a SQL expression {%mindt} something like this:

(
select min(`orderdate`)
from table A
where A.`acct`=table.`acct`
)

This would return the minimum order date per account. Then create a formula like this:

if {table.orderdate}={%mindt} then
"New" else
"Repeat"

-LB