Days between job raised and completed variable
Hi, im new to this website and semi new to Desktop intelligence. i have created a report that returns data by job number (i work at a housing association). the job number has 2 lots of data i am trying to interrogate, one is the time the job number was raised (i.e. 15/04/2008 09:30) and the time the job was completed (i.e. 15/04/2008 15:53), basically if the job is completed within 24 hours then we have hit out target if it is not we have missed, i need to measure this data.
sometimes a job can be put on hold before it is completed. (i.e. raised: 15/04/2008 09:30, on hold 15/04/2008 12:00, completed 15/04/2008 15:53) for some reason the variable doesnt count the jobs if they have gone on hold. so i am now using a new found variable of
"=((ToNumber(FormatDate(<Date Job Completed (Status 3)> ,"HH"))*60)(ToNumber(SubStr(FormatDate(<Date Job Completed (Status 3)> ,"HH:mm:ss") ,4 ,2)))+(DaysBetween(<Date & Time Job Raised> ,<Date Job Completed (Status 3)>)2460))/1440"
this above one works great however it slightly adds on an hour or two to the answer. (instead of being 0.97 days to complete it returns 1.20 days to complete)
there is no pattern in the extra hours it adds on at all.
can snyone see anything worng with the formual i am now using!?
any help would be appreciated,
Samantha Brown- Business Analyst