Averaging Time (HH:MM:SS) excluding Records with a time of Zero

[Melmeg]

Hello experts,

I am trying to get an average time in hh:mm:ss without counting the records with a zero value. From the results below you see the total is actually an average of all the 14 records but I only need an average of the four records that have a time. One of the column is in seconds and the other is converted to hh:mm:ss. Can someone please assist? The formula I used to convert the time is

NumberVar TotalSec := ({@AVGTime});
NumberVar Hours := Truncate (TotalSec / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;

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


Day @AVGTime hh:mm:ss @AVGTime in seconds
6/1/2011 00:00:00 0.00
6/2/2011 00:00:00 0.00
6/3/2011 00:00:00 0.00
6/4/2011 00:00:00 0.00
6/5/2011 00:09:57 597.00
6/6/2011 00:00:00 0.00
6/7/2011 00:00:00 0.00
6/8/2011 00:00:00 0.00
6/9/2011 00:11:52 712.00
6/10/2011 00:08:26 506.00
6/11/2011 00:00:00 0.00
6/12/2011 00:00:00 0.00
6/13/2011 00:00:00 0.00
6/14/2011 00:00:37 37.00
Total 00:02:12 132.29

 

[MCuthill]

Melmeg,

Very high-level, but what about a conditional "Count"? Increment a variable by 1 for each non-zero record and divide the total AVGTime in seconds by this integer.

Unfortunately I don't have crystal in front of me today for testing, but may be a start.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Melmeg]

Thanks Mike for your response. I have tried using a variable but cannot seem to get it right. May be I'm not defining them correctly. Can you please highlight me on how to go about it? This is what I have tried but doesn't work as desired.

Whileprintingrecords;
numberVar Total;
numberVar Counter;

If not isnull({@AVGTime}) and {@AVGTime} <> 0
then (
Total:=Total+ {@AVGTime};
Counter:=Counter+1;
);

if Counter > 0 then Total/Counter
else 0;

 

[MCuthill]

Melmeg,

It looks right to me. Though I beleive you need 2x formula's here - one for the running total calculations and one for the final "total average" calc.

Formula #1 (detail-level)
Whileprintingrecords;
numberVar Total;
numberVar Counter;

If not isnull({@AVGTime}) and {@AVGTime} <> 0 then
(
Total:=Total+ {@AVGTime};
Counter:=Counter+1;
);
---------------------------------
Formula #2 (report footer)
Whileprintingrecords;
numberVar Total;
numberVar Counter;

if Counter > 0 then Total/Counter else 0;
---------------------------------
If you are still having trouble, please place formula #1 in the details and let us know what is showing... perhaps seeing when the increments happen will shed some light.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Melmeg]

Thanks a lot! it works perfectly!