Averaging on a calculated formula

[BradW]

Good morning,

I have a report which details response times for emergency workers. Each incident is grouped by incident number and one or more responding units are contained in the group. I have a formula that calculates the response time for each unit. I then have a formula that displays the fastest response time (first unit on scene). I would then like to average the fastest response time for each incident to show an overall average response time. However, Crystal will not let me average on a calculated value. Can anyone provide a work around to this so I can do the calculation?

Thanks much in advance for your assistance!

 

[Katy44]

Can you insert (and maybe suppress) summary fields for sum and count of the above fastest response time formula, and then create a formula to find your average - sum/count? - I have had a similar problem and this seemed to work

 

[lbass]

It's a little hard to tell what might work without seeing the contents of all your formulas, but the following might work.

Create two formulas:

//{@sumcount} to be placed in the group (incident) header or footer:
whileprintingrecords;
numbervar min := min + minimum({@resptime},{table.incidentID});
numbervar counter := counter + 1;
numbervar ave := min/counter;

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar ave;

If this doesn't work, please provide the contents of each formula you are using.

-LB

 

[BradW]

First, thank you both for the reply. Here are the formulas:

Calculates time difference (located in details section):

Time({IIncidentUnitSummary.IIU_tArrive}-{IIncident.I_tTimeDispatch})

Calculates minimum response time (first on scene) (located in group footer of only group on report):

minimum({@Response Time},{IIncident.I_EventNumber})

 

[lbass]

I think my suggestion should work if you leave the time difference as a number, as in:

{IIncidentUnitSummary.IIU_tArrive}-{IIncident.I_tTimeDispatch}

Then you could convert it to a time value after the average is calculated. See faq767-3543 for a method for converting seconds to time.

If my suggestion doesn't work, please explain what results you get and provide more detail about your report design.

-LB

 

[BradW]

If you're still there....

Sorry about the delay it has been a really hectic week!

The format of the fields is date/time which may be causing some of the problems. What do you think about converting the date/time format to a number and then do the average and then convert back to a date/time?