# Calculating an Average from a formula field

I have a need to average each date the time taken to perform jobs. I have got the totals working now they want to average how long jobs take on a daily basis. I have attached a sample of the report and the formula used to work out the time taken.

Thanks

``````WhilePrintingRecords;
Local NumberVar TotalSeconds := DateDiff('s',DateTime(Date({tblJobJob.fldDateCreated}),Time({tblJobJob.fldTimeCreated})), DateTime(Date({tblJobJob.fldDateFinished}),Time({tblJobJob.fldTimeFinished})));
Local NumberVar TotalDays := 0;
Local NumberVar TotalHours := 0;
Local NumberVar TotalMinutes := 0;

TotalDays := TotalSeconds \ 86400;                //Seconds in a day
TotalSeconds := TotalSeconds mod 86400;

TotalHours := TotalSeconds \ 3600;                //Seconds in a hour
TotalSeconds := TotalSeconds mod 3600;

TotalMinutes := TotalSeconds \ 60;                //Seconds in a minute

ToText(TotalDays,0) & ' days ' & ToText(TotalHours,0) & ' hours and ' & ToText(TotalMinutes,0) & ' minutes.'
``````
Job-Request-times.JPG
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You need to add some global variables.

Name - DeclVars
WhilePrintingRecords;
Global NumberVar GrandTotalSeconds := 0;
Global NumberVar CountJobs := 0;

Global NumberVar GrandTotalSeconds;
Global NumberVar CountJobs;
CountJobs := CountJobs + 1;
GrandTotalSeconds := GrandTotalSeconds + TotalSeconds;

Add a formula to the footer where you want to display the average
Name - DispAverage
WhilePrintingRecords;
Global NumberVar GrandTotalSeconds;
Global NumberVar CountJobs;
Local NumberVar TotalSeconds := GrandTotalSeconds / CountJobs;
Local NumberVar TotalDays := 0;
Local NumberVar TotalHours := 0;
Local NumberVar TotalMinutes := 0;

TotalDays := TotalSeconds \ 86400;                //Seconds in a day
TotalSeconds := TotalSeconds mod 86400;

TotalHours := TotalSeconds \ 3600;                //Seconds in a hour
TotalSeconds := TotalSeconds mod 3600;

TotalMinutes := TotalSeconds \ 60;                //Seconds in a minute

ToText(TotalDays,0) & ' days ' & ToText(TotalHours,0) & ' hours and ' & ToText(TotalMinutes,0) & ' minutes.'

mlmcc
0
Author Commented:
Thank you, I have entered and now just having a little trouble not showing records and will get back soon with the results, or more help. Thank you again
0
Author Commented:
Can I push my luck here please
It worked great until I got smart and added in the date and time the request came in. So what this mean is, Billy Bob requested a job (Date and Time Recorded) and then maintenance created a job from that request (Date and Time) then it is Completed (Date and Time.)
I  tried the above formula for the request date and time and got "division by zero"  maybe because not all jobs are created via the request. some are jobs created by themselves. so my listing could have 60 jobs with 20% created from requests and the others created normally. but I can work with two if need be one for each type of job created., or if I could just use one date if the other is not there. I'm grabbing but the more you play with this stuff the clever you think you are but hte dumber I realise I am.

Thanks

If this is outside the scope please just let me know. As I really appreciate this help and am learning a lot
0
Commented:
YOu would need to test the field for NULL.

IsNull({YourFIeld})

mlmcc
0
Author Commented:
I'm back, I have put the isnull in about every spot and have been getting nowhere.

Can you let me know where I should put it. I did not want to play with the Del fields but I am still getting div by zero,
0
Commented:
WHat formula is giving the trouble?

mlmcc
0
Author Commented:
The Formula I am having trouble with I think, is the one I called (Dispaverage) Have attached the code below.  If the Finish Date is null. It seems to be. If I take this formula out of the report and it runs well, But when I put it into the report and filter on only show me between two dates, if there is a job that is not completed it gives the error.

``````WhilePrintingRecords;
Global NumberVar GrandTotalSeconds;
Global NumberVar CountJobs;
Local NumberVar TotalSeconds := GrandTotalSeconds / CountJobs;
Local NumberVar TotalDays := 0;
Local NumberVar TotalHours := 0;
Local NumberVar TotalMinutes := 0;

TotalDays := TotalSeconds \ 86400;                //Seconds in a day
TotalSeconds := TotalSeconds mod 86400;

TotalHours := TotalSeconds \ 3600;                //Seconds in a hour
TotalSeconds := TotalSeconds mod 3600;

TotalMinutes := TotalSeconds \ 60;                //Seconds in a minute

ToText(TotalDays,0) & ' days ' & ToText(TotalHours,0) & ' hours and ' & ToText(TotalMinutes,0) & ' minutes.'
``````
0
Commented:
You need to test the CountJobs for 0.

Local NumberVar TotalSeconds;
if CountJobs = 0 then
TotalSeconds = 0
else
TotalSeconds := GrandTotalSeconds / CountJobs;

mlmcc
0
Author Commented:
This is what I have at the moment and works except when It trys to devide the average into a record that does not have job request start date. gives divion by zero error.

WhilePrintingRecords;
Global NumberVar GrandTotalSeconds;
Global NumberVar CountJobs;
Local NumberVar TotalSeconds := GrandTotalSeconds / CountJobs;
Local NumberVar TotalDays := 0;
Local NumberVar TotalHours := 0;
Local NumberVar TotalMinutes := 0;

TotalDays := TotalSeconds \ 86400;                //Seconds in a day
TotalSeconds := TotalSeconds mod 86400;

TotalHours := TotalSeconds \ 3600;                //Seconds in a hour
TotalSeconds := TotalSeconds mod 3600;

TotalMinutes := TotalSeconds \ 60;                //Seconds in a minute

ToText(TotalDays,0) & ' days ' & ToText(TotalHours,0) & ' hours and ' & ToText(TotalMinutes,0) & ' minutes.'

When ever I try and put in the new section
Local NumberVar TotalSeconds;
if CountJobs = 0 then
TotalSeconds = 0
else
TotalSeconds := GrandTotalSeconds / CountJobs;
it tells me It expects a number, boolean or date here.  Culd you run your eyes over it please.
I have tried it instead of the localvar, and infront at the end.

Thank you.
David

0
Commented:
If that is the exact formula the first assignment is missing the :

Local NumberVar TotalSeconds;
if CountJobs = 0 then
TotalSeconds := 0
else
TotalSeconds := GrandTotalSeconds / CountJobs;

mlmcc
0
Author Commented:
Sorry for the delay, I tried rewritting my report in a differnt manner and it still did not work. I have attached a pic of what I have done and it has asked me as shown.
I tried it in all different formats as I know and decided to come back to the beginning

I hope you can help as I am getting behind in this report and already have another to do

thanks and regards
crystal-error.JPG
0
Commented:
You don't have CountJobs declared before you use it.

Put all the declarations together before the If statement.

mlmcc
0

Experts Exchange Solution brought to you by