Crystal: Count a field only if another field has occured before it

[turtlepatch]

I am working in Crystal 9.2 with an ODBC database. I have data that displays Job-id's, Location-id's, and Date of location change. Each job will have several location id's and I am trying to count the number of jobs in each location at the time of the prompted date. For example:

job-id location-id date
1 15 05/01/2006
1 22 05/04/2006
2 22 05/01/2006
2 15 05/02/2006
2 22 05/04/2006

I then have formulas so that I can count the locations.

@Layout if {JobLocationChange.Locate-ID}="15" then 1 else 0

@ProofOut if {JobLocationChange.Locate-ID}="22" then 1 else 0

@Corrections needs to state if 15 happened after 22 then 1 else 0

So if you follow the example above and the date selected was 05/06/2006 then I would have:
ProofOut = 2

But if you have a date of 05/03/2006 then there would be a count as follows:
Layout = 1
Corrections = 1

I'm not sure how to get the count of the Correction locations. Any help is greatly appreciated.

Thanks
turtle_patch

 

[lbass]

You should either group on job ID or sort on this field and then do a secondary sort on date. Then you can insert a running total that is a distinctcount of job ID, evaluate using a formula:

jobID = previous(jobID) and
locationID = 15 and
previous(locationID) = 22

Reset never.

-LB

 

[turtlepatch]

Thanks! That worked perfect.