Preventative Maintenance - Mean Time Before Failure

[BonganiButh]

Hi All 

I hope everyone is good. 

I am trying to calculate the MTBF for each SITE ID. The  PMs are labeled as 'PM', so I need to find the difference between the Resolved DateTime of the "PM" and the OpenDateTime of the next "ADDRESSABLE" call type( next Date after the ResolveDateTime of the PM) as seen below.  

I have tried the below and have seen not been successfully to get the results I want ( The difference between the two dates)

NB: Data is sorted by the Resolve DateTime

Formula: Aggr( [Resolved Date Time] -Above([open Date Time]), [Site ID], [Call Type].[Request Type])

 Your assistance will be highly appreciated.  🙂 

Kind regards,

Bongani 

[eddie_wagt]

I would suggest you fix this in the load script. Something like this

TMP:
LOAD * Inline [Request ID, Call Type, Request Type, Site ID, open Date Time, Resolved Date Time
1, ADDRESSABLE, RPR, 1, 2019-05-14 11:47:00.000000, 2019-05-14 14:04:00.000000
2, NON-ADDRESSABLE, PM, 1, 2019-03-21 16:08:00.000000, 2019-04-26 13:46:00.000000
];

TMP2:
NoConcatenate
LOAD *
, interval([Resolved Date Time]-previous([open Date Time]),'dd hh:mm') as Interval
Resident TMP
Order By [Resolved Date Time] asc;

drop table TMP;

Regards Eddie

[BonganiButh]

Hi  @eddie_wagt 

Thanks for your quick response. 

Is there a way in the load script we can include code to only subtract from the next OpenDateTime. In the example above the Resolve_datetime for the PM is (2019-04-26  13:46) and the next OpenDateTime for RPR is (2019-05-14   11:47)

so I would like to get the difference between these two dates only(OpenDateTime - Resolve_datetime? 

[eddie_wagt]

It should be possible, but can you share the data in an other way so I can help you better? A small sample will do.

[BonganiButh]

Hi @eddie_wagt 

Attached is the sample data. I hope this is helpful.The file is sorted by the resolve_date 

Aim: To find the difference between a PM (resolve_date_time) and the next (open_datetime) for an Addressable Call type.

[eddie_wagt · Accepted solution]

It is almost weekend and I have little time this day or next week. I tried to script quickly something together. Maybe this is not the most optimized way to do this, but maybe there are some other members of the community who can support you. 

Tab1:
LOAD [Request ID], 
     [Call Type], 
     [Request Type], 
     [Site ID], 
     [open Date Time],
     [Resolved Date Time]
FROM
[MTBF Sample Data  .xlsx]
(ooxml, embedded labels, table is Sheet2)
;

Tab2:
LOAD RowNo() as RecNo
,	 if([Call Type]='ADDRESSABLE',rangesum(FLAG,peek(TMP))+1) as TMP
,	 *
;		
LOAD if([Request Type]='PM',1,0) as FLAG
,	 *
Resident Tab1
Order by [Site ID],[Resolved Date Time] asc
;

left join (Tab2)
LOAD [Site ID]
,	 min(RecNo) 	  as RecNo
,	 [open Date Time] as [Next open Date Time tmp]
Resident Tab2
Where TMP=1

Group By [Site ID]
, TMP
,[open Date Time]
;

drop table Tab1;

Tab3:
NoConcatenate 
LOAD *
,	 RecNo as RecNo2
,	 peek(TMP) as TMP2
,    peek([Next open Date Time tmp]) as TMP3
Resident Tab2
Where TMP=1 or FLAG>0
Order by [Site ID],[Resolved Date Time] desc
;

left join (Tab2)
LOAD RecNo2 as RecNo
,	 TMP2   as FLAG
,	 TMP3   as [Next open Date Time]
Resident Tab3;

drop table Tab3;

Final:
LOAD [Request ID] 
,	 [Call Type] 
,	 [Request Type] 
,	 [Site ID]
,	 [open Date Time]
,	 [Resolved Date Time]
,	 Interval([Next open Date Time]-[Resolved Date Time],'dd hh:mm:ss') as Difference
Resident  Tab2
Order By [Resolved Date Time] asc;

drop table Tab2;

[BonganiButh]

Thank you so much for your time and efforts on this @eddie_wagt . I will have a look at the recently shared script and provide feedback. 

Really appreciate it. 🙂  

[BonganiButh]

Hi @eddie_wagt 

This does exactly what I am looking for. I have since labelled this is the 'Accepted Solution'. 

Next round is definitely on me. 😉

Thanks once again.

Thanks again, this has solve