Trouble calculating average times 1

[mattjol]

Good afternoon,

This will probably go without saying after reading the post but I'm new to formula writing...

I am currently trying to provide a report that details ambulance transport times and then calculates average times for all transports.

Transport times are calculated by determining the difference between the time they call "en route" and the time they call "arrived". We use the codes "EH" and "AH" in the system to generate time stamps for each event. The codes are not always entered consecutively and because of that, I had to use the following formula to make sure the calculation worked correctly:

numberVar dur:=

DateDiff("s",minimum({rlmain.logdate},{cdcall.number}), maximum({rlmain.logdate},{cdcall.number}));

numberVar Hrs;
NumberVar Min;
NumberVar Sec;
StringVar HHMMSS;

Hrs:= Truncate(Truncate (dur/60)/60);
Min:= Remainder(Truncate(dur/60),60);
Sec:= Remainder(dur,60);

HHMMSS:= totext(Hrs,"0") + ":" + totext(Min,"00") + ":" + totext(Sec,"00");

HHMMSS

At the end of the report, I tried to get the average of these times by using this formula in the "Average Response Time Field":

Average ({@Elapsed Time})

where "Elapsed Time" is the calculated transport time from the formula above.

The problem is that I am getting an error in the formula that says "A number field or Currency Amount Field is required here"

Is there a better method to generate the transport times and if not, how might I avoid the error in the "Average Response Time" field.

Thanks,


Matt

 

[lbass]

You should only use the formula above to translate results into a string. For calculations, you should use the datediff formula in a formula like this following:

whileprintingrecords;
numbervar secs := DateDiff("s",minimum({rlmain.logdate},{cdcall.number}), maximum({rlmain.logdate},{cdcall.number}));
numbervar sumsecs := sumsecs + secs;
numbervar cnt := cnt + 1;

Place the above in the logdate group section and suppress it. Then in the report footer, use this formula to display the average:

whileprintingrecords;
numbervar sumsecs;
numbervar cnt;
sumsecs/cnt

Then you can apply the formula to convert to a string by setting dur := sumsecs/cnt.

-LB

 

[mattjol]

Thanks for the reply LB!

I get odd results when using the formula you suggested. When I use the first two lines:

whileprintingrecords;
numbervar secs := DateDiff("s",minimum({rlmain.logdate},{cdcall.number}), maximum({rlmain.logdate},{cdcall.number}));

I get what looks like the seconds as seen in the attachment.
If I include the last two lines:

numbervar sumsecs := sumsecs + secs;
numbervar cnt := cnt + 1;

It seems to produce a count of the total records in the report as seen in the second attachment (Transport Times 2.pdf)

Currently the report is grouping by "Call number" (cdcall.number) and I put the formula in the "elapsed time" field for each group.

When I put your suggestion for average time in the footer, it produced a number 697.50 but I'm not sure if that is accurate based on the numbers that are being summed.

Lastly, when you said to apply the formula to convert to a string, are you referring to the field format?

Thanks for helping the slow newbie with this...I really appreciate it!!

 

[lbass]

The first formula should be in your callnumber group, and the second formula should be in your report footer, if you want a report average. Why do you think the numbers are funny? I can't really troubleshoot this. The first formula adds the datediff for each group and also counts the groups, and the second formula provides the average calculation. Nothing in your files looks wrong to me. If you want the first formula to display the difference per group, then add a final line to it and don't suppress it:

whileprintingrecords;
numbervar secs := DateDiff("s",minimum({rlmain.logdate},{cdcall.number}), maximum({rlmain.logdate},{cdcall.number}));
numbervar sumsecs := sumsecs + secs;
numbervar cnt := cnt + 1;
secs

-LB

 

[mattjol]

Thanks LB,

The number that was showing was the "cnt" and although I thought that's what it was, I didn't want to assume. Adding the last line for "secs" correctly displayed the elapsed time in seconds. I do want to convert those seconds to to properly display hours:minutes:seconds. If I do that for each group, can I then do that for the average as well?

Thanks again for all your help...any recommendations you can make on resources for learning formulas?

Matt

 

[mattjol]

LB,

Maybe the better question to ask is...would it be more appropriate to put the conversion formula in the field format...ie..put it in the "display string" formula or as part of the actual field formula?

Thanks,

Matt

 

[lbass]

Yes, you can convert for groups and for the average. Ken Hamady is a good resource for formulas (

http://www.kenhamady.com),

and then just following Tek-Tips threads will be helpful.

-LB

 

[mattjol]

Good afternoon LB,

Found a conversion on Hamady's website to convert from seconds to days:hours:minutes:seconds. It works fine for each group but when I use it in the "Average" field, to convert the average seconds to the days:hours:minutes:seconds format, it displays the same value from the "elapsed time" field of the very last group in the report. This is the conversion formula I'm using:

WhilePrintingRecords;
NumberVar TotalSec := {@Elapsed Time};

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 ( Days , '##' ) + ':' +
Totext ( Hours , '00' ) + ':' +
Totext ( Minutes , '00' ) + ':' +
Totext ( Seconds , '00' )

The formulas in both the elapsed time and the average time fields work great on their own...it seems to be the conversion in the average field that is problematic.

Is there something I need to do to make it convert the value in the "average" field as opposed to the value of the last group? You can see an example of what I'm referring to in the attachment.

Thanks for all your help on this..you've been invaluable!
Matt

 

[lbass]

You shouldn't be referencing my first formula (if that's what you are doing). You should be referencing the display formula (second formula), or better yet, doing the conversion right in the display formula by setting totsecs := sumsecs/cnt;

-LB