amount for max date

[fancyface]

Hi there. I'm using Crystal 7. I know how to get max date which gets me the most recent date - how do I get the amount for the most recent date? If I use max(amount) then I'll get the largest amount which is what I don't want. I'm grouping on customer id. Thanks.

 

[lbass]

Go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.customerID})

This will return the most recent record for display, which will include the corresponding amount. Note that you would then have to use running totals to summarize across groups, since the non-group selected records will still contribute to inserted summaries.

-LB

 

[fancyface]

I don't necessarily want to do anything with a record selection because also in the same report I want to use all my amounts for a different calculation.

 

[dgillz]

In that case, use variables. Put the following formula in the details section:

WhilePrintingRecords;
OnFirstRecord then Numbervar GrpMax:={YourNumberField} else
if {YourDate}>Previous({YourDate}) then Numbervar GrpMax:={YourNumberField}

In the report footer:

WhilePrintingRecords;
Numbervar GrpMax;

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

You would also need a reset formula for Don's approach or mine below:

whileprintingrecords;
numbervar GrpMax;
if not inrepeatedgroupheader then
GrpMax := 0;

Another way to set this up would be to use a detail formula like this:

whileprintingrecords;
numbervar GrpMax;
if {table.date} = maximum({table.date},{table.customerID}) then
GrpMax := {table.amt};

Then in your group footer, use the formula:
whileprintingrecords;
numbervar GrpMax;

-LB

 

[dgillz]

My bad, I forgot about the grouping. I like LB's formula better anyway.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry