time average

[michelin]

Hi,

I have a problem to count my average time.

I have a special situation. I have a couple of incidents that I need to exclude (incident D1) from counting the average but I still need to see them in my report.
Incident start end difference
V1 00:20:20 00:20:26 00:00:06
V2 14:15:16 14:16:16 00:01:00
D1 15:13:12 15:13:15

what formula should I use for counting the average time (in my case it would be 00:00:33)?

Thank you for any advice!

 

[Madawc]

You could do a Running Total with a text that excludes them. Or a formula field that is 0 for those values and 1 for anything else, and do a summary count for that.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[michelin]

Oh, I'm sorry. I forgot :/. I'm using Crystal reports 12 (2008).

Thank you for your reply!
What do you mean running totals? For difference field I'm using formula:
if incident like V then {@end-start}. that's how I'm excluding incident D.
If I do the average thru running total and I do:
field to summarize: difference
for the type of summary i have options: maximum, minimum, count, distinct count, Nth largest, Nth smalest, mode, Nth most frequent. (I think that I need to convert my difference field to something).

I'm pretty familiar with automated totals, running totals, using formula field 0/1.. But I really don't know what to do in this specific case .

 

[lbass]

Create a formula like this:

if {table.incident} like "V*" then
{@end-start} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything. You should then be able to right click ont his formula and insert counts or averages, etc., without the irrelevant rows contributing.

-LB

 

[michelin]

Thank you lbass,

but for second part (@null) it says that a time is requiered here.
what i did was that i run total:
field to summarize: datediff ("s",{start},{end})
type of summary: average
evaluate: I use a formula: {table.incident} like "V*"
reset: never.

i almost got it right but:

one incident contains couple of start times. i needed the earliest one. so i sorted the start field (ascending) and then i suppressed the other lines.. but my average counts also suppressed results.. any idea why? (hope i explained it clearly).

 

[lbass]

If you have multiple records, you should use a running total that does an average of my formula, evaluate on change of incident (either group or field), reset never. Place the running total in the report footer.

-LB