Return the second earliest date 1

[tsouth4]

I'll try my best to explain what i'm trying to do. I have a report that returns several rows of data for each customer. My data is grouped by customer and I have been asked to display the minimum order date and the second order date. For example a customer that placed 4 orders but i only want to display the order dates for the first and second orders in separate columns.

Customer OrderDate
xxx 01/01/11
xxx 01/15/11
xxx 01/31/11
xxx 02/15/11

So for my example above the group footer should read. As I mentioned i'm hiding the details and grouping by customer.
Customer 1st Order Date 2nd Order Date
XXX 01/01/11 01/15/11

I tried sorting by Order placed date and creating a running total on customer however this doesn't seem to work.
Any help would be greatly appreciated.

Thanks,

 

[lbass]

Use the group header instead, and drag the order date into the group header, and then create a formula for the second order date:

nthsmallest(2,{table.orderdate},{table.customer})

-LB

 

[tsouth4]

Thanks, worked perfectly...

 

[tsouth4]

Actually I just realized that this isn't working 100% correctly. My data returns multiple rows for some orders in that it returns a row for each item placed. I need it to return the second date rather than the second row.

Customer OrderDate
xxx 01/01/11 12:15am
xxx 01/01/11 12:15am
xxx 01/01/11 12:15am
xxx 01/15/11 1:45pm
xxx 01/31/11 6:25pm
xxx 02/15/11 4:32am

Thanks,

 

[lbass]

Okay, a different approach then. Create these formulas:

//{@detail formula}:
whileprintingrecords;
datevar two;
numbervar cnt;
if onfirstrecord or
{table.customer} <> previous({table.customer}) or
date({table.orderdate}) <> date(previous({table.orderdate}) then
cnt := cnt + 1;
if cnt = 2 then
two := {table.orderdate};

//{@reset} for customer group header:
whileprintingrecords;
datevar two;
numbervar cnt;
if not inrepeatedgroupheader then(
two := date(0,0,0);
cnt := 0
);

//{@display} for the customer group footer:
whileprintingrecords;
datevar two;

Insert a minimum on {table.orderdate} for the first record, and then drag the custome groupname into the group footer and suppress the detail and group header sections.

-LB

 

[tsouth4]

I had to tweak your formulas slightly in order to make it a datetimevar but it worked like a charm. Thanks for your help LB.