Calculating Average elapsed times 1

[susanna123]

Hi

I was wondering if you can tell me how I can code this:

There are 5 dates/times with start and end in the format below:

Start End
01/05/2010 02:44:47 01/05/2010 03:22:09
01/05/2010 02:37:59 01/05/2010 07:16:27
01/05/2010 02:40:00 01/05/2010 03:23:03
01/05/2010 02:40:00 01/05/2010 03:15:17


For example, if the elapsed time for the first 4 is 20 minutes and a fifth time was 40 min. The average should be 24min, elapse time of 40 min and total time would be 40 min.

How can this be coded using the above example?
Thanks.

 

[lbass]

Your sample makes no sense in relation to the results you are expecting. The second record alone has a difference of over four hours. Why are the records not sorted by starttime? How did you arrive at your results?

-LB

 

[susanna123]

Hi Lb,

The actual question is really this: For example, if the elapsed time for the first 4 is 20 minutes and a fifth time was 40 min. The average should be 24min, elapse time of 40 min and total time would be 40 min.

the dates and time above are just an example to show the format.

Thanks again.

 

[lbass]

Can't help unless you provide a sample that corresponds to your sample results.

-LB

 

[kenhamady]

If I understand this correctly, a general answer is that you have to get the elapsed time for each record converted into seconds. Then you can average the seconds values, then you can convert it back to elapsed time with a formula like this:

http://www.kenhamady.com/form09.shtml

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[susanna123]

I have start and end times and have calculated the time elapsed which is difference between start and end time. I would like to calculate the average of the elapsed time.

A formula field has been created for elapsed time.

The code is as follows:

Local NumberVar sec := DateDiff ('s',{@Instance Start Time} , {@Instance End Time}) ;
//sec := 12500 ;
Local NumberVar hrs := Truncate(Remainder(sec,86400)/3600);
Local NumberVar mns := Truncate(Remainder(sec,3600)/60);
Local NumberVar scs := Remainder(sec,60);

ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00')

I would like to create a formula field to calculate the average of elapsed time. It would show the averages for every elapsed time

Can you show me how this can be done?
Thks.

 

[kenhamady]

You can't average it once it is text, you have to average it first. Start with a formula called @seconds:

DateDiff ('s',{@Instance Start Time} , {@Instance End Time})

Then create another formula to display the average the way you want, like this:

Local NumberVar sec := Average ({@seconds}) ;
//sec := 12500 ;
Local NumberVar hrs := Truncate(Remainder(sec,86400)/3600);
Local NumberVar mns := Truncate(Remainder(sec,3600)/60);
Local NumberVar scs := Remainder(sec,60);

ToText(hrs,"00") + ":" + ToText(mns,'00') + ":" + ToText(scs,'00')

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[susanna123]

Using the above formula, It appears that the average time is the same for all cases. Here is an example:

Case 1:

start time: 21:34:25
End time: 21:42:36
Time elapsed: 00:08:11
Average time elapsed: 00:06:42

Case 2:

Start time: 13:58:11
End time: 14:04:09
Time elapsed: 00:05:58
Average time elapsed: 00:06:42

Is there something wrong with the calcullations?
Thks.

 

[kenhamady]

Given those two cases what did you expect to see.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[susanna123]

hi, my question is should the average be the same for both cases. There are other cases too but it shows the same average elapsed time. Thks.

 

[kenhamady]

The average is currently the average for all the records in the report so yes, it will be the same for all cases. There is only one average.

Now if you can tell me what the correct numbers should have been for those two cases, I think it will clarify things for both you and me.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[susanna123]

Example

start time:

14:56:29
14:56:17
15:04:10
15:02:14
15:03:33

End time:

15:29:15
15:36:49
15:51:18
15:51:46
15:52:56

Time elapsed

00:32:46
00:40:32
00:47:08
00:49:32
00:49:23

I would expect to see average time to be 43:52

 

[susanna123]

Actually I think i'm able to figure the above.
What i'm having trouble is this scenario here:

5 elapsed times:

1. 20 minutes
2. 20 minutes
3. 20 minutes
4. 20 minutes
5. 40 minutes

the average of those 5 times are 24 minutes: 20+20+20+20+40/5

The elapsed time is 40 minutes and total time is 40 minutes

I only want to display the times that are over 30 minutes. In this case row 5 but still be able to show the average time of 24 minutes for the entire report.
I have a parameter created that allows user to select the processing time in seconds. If i filter it just 40 minutes or 40x60 = 2400 in seconds. It will only show row 5 and the average would not be 24 minutes but instead 40 minutes.

Hope this clarifies and helps you to unstand what I'm looking for. Thks

 

[kenhamady]

Instead of filtering out the records that you don't want to see, suppress the section for those records.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[susanna123]

is there a code that will need to be written in order to perform that action?

 

[kenhamady]

Go into the section expert and click the formula button for the suppress property of the section that displays. Put in something like this:

{@elapsed seconds} < 30*60
//or 40 or whatever your minimum is to display

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[susanna123]

Thanks Kenhamady.

I will require all the parameters to be there.
I currently have a start and end time parameter and a processing time parameter.
Is there a way to allow it so that these fields can be left blank and when the report runs, it will run for all records.

Your advice is appreciated.thanks.

 

[kenhamady]

I think you should start a new question for this part.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[susanna123]

The average should be for all report runs whether or not all the times meet the criteria for the report and where users can still be able to use the parameters to filter the criteria.

In my above example:

the parameter value i selected for processing time is anything greater or equal to 20 minutes ie) 1200 seconds

5 elapsed times:

1. 20 minutes
2. 20 minutes
3. 20 minutes
4. 20 minutes
5. 40 minutes

the average of those 5 times are 24 minutes: 20+20+20+20+40/5

The elapsed time is 40 minutes and total time is 40 minutes

All 5 records are 20 minutes and above, hence they will be in red.

The average is displaying only for those 5 records.

The next time i run it with a different processing time, the average time would be totally different.

Please advice. thks

 

[kenhamady]

Parameters that should control which records are included in the average should go in the record selection formula. Any parameters that only affect the displaying of the records should go into the suppress formula. The suppress formula has no affect on totals so suppressing doesn't change the average.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/