• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2964
  • Last Modified:

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.'

Open in new window

Job-Request-times.JPG
0
thirdcity
Asked:
thirdcity
  • 7
  • 6
1 Solution
 
mlmccCommented:
You need to add some global variables.

IN the report header add a formula
Name - DeclVars
WhilePrintingRecords;
Global NumberVar GrandTotalSeconds := 0;
Global NumberVar CountJobs := 0;

Modify your formula by adding these lines
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
 
thirdcityAuthor 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
 
thirdcityAuthor 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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
mlmccCommented:
YOu would need to test the field for NULL.

IsNull({YourFIeld})

mlmcc
0
 
thirdcityAuthor 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
 
mlmccCommented:
WHat formula is giving the trouble?

mlmcc
0
 
thirdcityAuthor 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.'

Open in new window

0
 
mlmccCommented:
You need to test the CountJobs for 0.

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

mlmcc
0
 
thirdcityAuthor 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
 
mlmccCommented:
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
 
thirdcityAuthor 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
 
mlmccCommented:
You don't have CountJobs declared before you use it.

Put all the declarations together before the If statement.

mlmcc
0
 
thirdcityAuthor Commented:
Sorry for the delay, I have put that together, funny how it works when i finally get my act together. I thank you for the assist and look forward to my next question. Much appreciated.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now