Maximum date where less than Start Date

[h20vrrq]

Hi,

I am trying to write a report that will let me find the last category date & category for each animal in the table where the category date is less than a parameter date. There will be many category dates, some before the parameter date and some after the parameter date. I know how to work out the maximum date for each animal, but not how to work it out where the date has to be less than the parameter date.

I am using CR XI.

Any ideas?

 

[lbass]

Can you clarify whether 1) you just want to the most recent date that is less than the parameter even if there are more recent dates than the parameter date, or
2) you want to find those animals where the most recent date is less than the parameter date.

-LB

 

[h20vrrq]

Hi,

I will need to identify each animal.

The table looks something like this (an example of one animal record): -

AnimalRef CategoryDate Category
300004 10/05/2008 New
300004 25/09/2008 Training
300004 17/12/2008 Transfer
300004 02/01/2009 Training
300004 11/02/2009 Complete

If the report was looking for the last category where the report start date was 01/01/2009 then from the above record I would want to find the Transfer on 17/12/2008.

As the final stage of the report I need to calculate how many animals were in what category before the report start date.

Thanks

 

[Madawc]

Do you need the details that aren't before the start date? If you don't, you could get rid of them in Record Selection.

Assuming you do, add an extra group. I assume you are grouping for AnimalRef: add a formula field that splits by Start date, e.g.

if {CategoryDate} < {Param.Date} then "Before" else "Not Before"

You can now add it as a lower-level group and use the maximum for that group.

It could also be done by variables, but this is probably easier.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Then use a record selection formula like this:

{table.categorydate} < {?startdate}

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

{table.categorydate} = maximum({table.categorydate}, {table.animalref})

Then create a formula {@lastcateg}:

if {table.categorydate} = maximum({table.categorydate}, {table.animalref}) then {table.category}

Insert a crosstab in the report footer and add {@lastcateg} as your row field and add distinctcount of {table.animalref} as your summary field.

-LB