Average of a time field CR9 1

[TimBiesiek]

Hi All,

Using CR9.

Am trying to get the average start time and finish time for a group of workers.

Have a field (RTime) that gives a whole lot of activity times, and am using the min and max of this, grouped by an RDate field to get the start and finish times for each user for each day.

I then want to average the start time and finish time for each team of users, but can't get a summarise the Min or Max RTimes...

Any help?

Thanks!!!

 

[lbass]

I think you will first have to convert your minimum (and maximum) times to seconds in a formula like {@secs}:

DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),minimum({@time},{table.user})))

This assumes you have a group on {table.user}.

Then use a variable to accumulate the results:

//{@accum} to be placed in the group header or footer:
whileprintingrecords;
numbervar secs := secs + {@secs};

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

secs/distinctcount({table.user})

You can then convert the average seconds back to a time format by using a formula like that found in FAQ767-3543.

-LB

 

[TimBiesiek]

Hi LB,

Tried your formula above, changing it to fit my tables:

DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),[red]minimum({cur_FieldResult.RTime},{cur_FieldResult.RDate}[/red])))

but it says a time is required here... And the minimum expression is highlighted....

Any thoughts?

 

[TimBiesiek]

Ah, just got it with a ctime().... Will let you know how I get on with the rest of it!

 

[TimBiesiek]

LB, you're a champ! Have a star! Works a charm now!

 

[TimBiesiek]

Hmm, I run into troubles though, when I am trying to convert 271440 seconds, back into a time...

It ends up being 75:24:00.... either this is some time in the middle of the night when I'm fast asleep, or the conversion bit can't handle that many seconds...

Or, perhaps, my averaging formula isn't working right...

 

[lbass]

Not sure how you want to display the days, but try the following:

numberVar dur := DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),ctime(minimum({cur_FieldResult.RTime},{cur_FieldResult.RDate}))));
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmm;

days := Truncate(dur/86400);
hrs := truncate(remainder(dur,86400)/3600);
min := truncate(remainder(dur/60,60));
sec := remainder(dur,60);

ddhhmm := totext(days,"00")+" days " +
totext(hrs, "00") + ":" +
totext(min, "00") + ":" + totext(sec,"00");

-LB

 

[TimBiesiek]

Thanks LB!

It turns out my averaging formula wasn't right.... it wasn't dividing by the right number.... got it sorted now though! Cheers!