suppress if duplicate and counting average time...??? 1

[michelin]

I'm new in crystal reports, any advice would be appreciated!!

I have a reports, where it shows:

'incident' 'district' 'time starts' 'time ends'


I had couple of duplicates so i put formula 'not onfirstrecord and {incident}=previous{incident}' to suppress the duplicates field.. now i need to count the average times for each district.. but it counts also times of suppressed incidents. is there any formula how to exclude them? I guess i should use distinct count, but i don't know how to write it..

my other question is.. i know how to write the formula for average times for the district, but i need the average time also for months- january, february.. any help?

thank you very much!

 

[Madawc]

Do a Formula Field with a conditional count, e.g.

if onfirstrecord or{incident}<>previous{incident}
then 1
else 0

The sum of @TrueCount should be the number of non-duplicates.

The use of Crystal's automated totals is outlined at FAQ767-6524. I'd suggest you spend a few hours checking all they can do: it will pay off in the longer run.

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

 

[lbass]

Try using a running total that does an average of elapsed time, evaluate on change of group: incident (add a group #2 on incident if you don't have one), reset on change of group: district (group#1).Place the result in the district group footer.

Not sure at what level you want to evaluate months--is this within or across districts?

-LB

 

[michelin]

thank you very much!! the think is that i tried to do formula:
if not onfirst record and incident=previous(incident) then 'delete' and then just filter it out. but the formula is not in the filter (i don't know why). that's why i had to do suppress...

here are my formula for the averages:

NumberVar TotalSec := Average ({@TIME_SEC},{@Districts});
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;

Totext ( Hours , '00' ) + ':' +
Totext ( Minutes , '00' ) + ':' +
Totext ( Seconds , '00' )

where {@TIME_SEC} is:
DATEDIFF ("s",{Response_Master_Incident.Time_First_Unit_Assigned},{Activity_Log.Date_Time})

so my problem is how to make this formula work just for non-suppress data?


hope it makes a sense...

 

[lbass]

This formula is only for converting a number of seconds to a string--you can't calculate with it. If you want to show the result as a string, then you would plug in the running total in place of Average ({@TIME_SEC},{@Districts}) and then display the result in the districts group footer.

-LB

 

[michelin]

thank you, thank you, thank you!!!!!

so i made a progress and i finally realized what you were talking about. i'm sorry it last a while.. i'm new in crystal reports.

so my progress is that now it doesn't count non-suppressed unit (that's what i wanted thank you!) but the total is in the seconds: 366 seconds.. is there a way how to convert it to the hh.mm.ss format?

thank you so much for all your help!! you made my day and week much much more easier!

 

[lbass]

Once you have the running total, place it into the conversion formula you showed earlier like this:

NumberVar TotalSec := {#yourrunningtotal};

-LB

 

[michelin]

I can't thank you enough!!