How can a subtract dates to find a mean time?

[CognosNewbie99]

I'm using Crystal 8 with Maximo v4 and I'm having a little bit of trouble. I'm pulling in equipment along with work orders written against those pieces of equipment. I have an actual finish field on the work orders. I'd like to subrtact one actual finish date from another one the same piece of equipment in order to find a mean down time. How can I do that? I tried doing actfinish - actfinish but that doesn't work.

 

[lbass]

But wouldn't it be the difference between the finish date and the start of the next work order? Not sure what you mean by "down time" here. Maybe supply some mock data that illustrates what time intervals you want to measure. Please show equipment and a couple of work orders with both start and finish dates.

-LB

 

[Madawc]

In Crystal 8.5, you can use DateAdd to subtract two dates, date-times or times from each other. Use Crystal's HELP function to find how to use it, assuming your version has it.

Once you have the difference in minues, averaging should be easy.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[CognosNewbie99]

eqnum
wonum status worktype actstart actfinish Mean Failure Time
1023
56437 CLOSE CM 8/31/2011 8/31/2011 0.00

BP000002
51995 COMP CM 12/20/2010 12/20/2010 0.00

BP000016
51551 COMP SA 11/24/2010 1/5/2011 0.00

BP000018
52616 COMP CM 12/27/2010 1/4/2011 0.00

BP000023
58684 APPR CM

BP000034
53335 COMP CM 5/16/2011 5/16/2011 0.00
57041 COMP CM 8/5/2011 8/8/2011 0.00

BP000037
54511 COMP CM 5/31/2011 6/1/2011 0.00

BP000041
TEX44270 COMP CM 2/4/2010 2/4/2010 0.00

BP000092
56240 COMP CM 6/27/2011 6/27/2011 0.00
56274 APPR CM

BP000109
60028 COMP CM 1/19/2012 1/19/2012 0.00
60555 COMP CM 2/6/2012 2/6/2012 0.00
57058 COMP CM 8/24/2011 8/24/2011 0.00
57582 COMP CM 10/18/2011 10/18/2011 0.00


Here is some test data. Grouped by equipment number, then listing work order number, status, worktype, actual start, actual finish, and the data I pull with my formula, which is 0. I thought I would using actual start and actual finish to find the down time but the client wants it from actual finish of one work order to actual finish of another. I'll try the DateAdd and see how it works.

 

[lbass]

You appear to have some work orders with null dates. First eliminate them from the report in the selection formula by using:

not isnull({table.actstart})

Sort the records by actfinish in ascending order.

Then create the following formulas:

//{@reset} to be placed in the equipment group header:
whileprintingrecords;
numbervar diff := 0;
numbervar cnt := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar diff;
numbervar cnt;
if {table.equipment} = previous({table.equipment}) then (
diff := datediff("d",previous({table.actfinish}),{table.actfinish});
cnt := cnt + 1
);

Then display the result for that equipment in the group footer:
//{@display}:
whileprintingrecords;
numbervar diff;
numbervar cnt;
if cnt <> 0 then
diff/cnt

-LB