NthSmallest

[Edie0802]

I am new (6th months) to Crystal Reports.

I have a report in which I have two groups: Group 1 is grouped by an number unique to our application(called apbldg.apno in this example). Group 2 is grouped by a Date Calculation that I do which consists of an Issue Date minus a Processeed Date.

On this report, some of my application numbers do not have Issue Dates yet.

I need to pick out the second smallest Date Calculation so that I can exclude those that have no date calculation. For example, I would need to pick out 11.89 in this example:

Ap No Processed Date Issued Date Date Calculation

1234 4/24/03 9/11/03 139.73
3456 6/18/03
2578 9/11/03 9/23/03 11.89

I have looked into Crystal help and found that I can do this:

NthSmallest(2, @DateCalc, Bldg.Apno). I try this, but get nothing on the return.

If I do this:
NthSmallest(1, @DateCalc, Bldg.Apno), I get 0.00.

I have tried minimum function. I have also tried to count the non-unique apno's in which the Issued Date is null, and haven't been able to get anywhere.

I've never been able to get array's to work with our application. There is no one at my work who knows more about Crystal than I do. I am stuck.

Thanks.

 

[synapsevampire]

Since these ap_no's are all different it makes sense that grouping by them in the nthsmallest wouldn't work. What set of data is this the smallest of?

You want the smallest calculation for the entire report?

-k

 

[Edie0802]

-k, thank you for responding so quickly.

Yes, I want the smallest calculation for the entire report in which the Date Calculation is populated. In this simplified example, I would want to pull 11.89 because it is the lowest, excluding record 3456 which doesn't have the date calculation.

Ap No Processed Date Issued Date Date Calculation
1234 4/24/03 9/11/03 139.73
3456 6/18/03
2578 9/11/03 9/23/03 11.89

-e

 

[synapsevampire]

Try:

Report->Edit Selection Formula->Group

minimum({@DateCalc}) = @DateCalc

and then do:

Report->Edit Selection Formula->Record

@DateCalc > 0

should do it.

You may get duplicates of course.

-k

 

[Edie0802]

-k:

I got results with the part of your suggestion that at the Record Level I put {@DateCalc} > 0. The other part of your suggestion, that I use minimum({@DateCalc}) = @DateCalc at the Record level I did not need.

I am getting the minimum now that I am supposed to because I have eliminated all the records without a Issued Date from the start.

I just now have to check with the boss to see if that's what she wants. Thank you for your help.

-e