Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I work in an Industrial environment, and a key KPI is plant reliability, availability, MTBF (Mean Time Between Failure) etc. This implies recording all stoppages by Time Stopped and Time Started. Obviously, as a piece of equipment has no sense of time, they will break across days, making it a bit difficult to keep track of my stoppage times per day. This information is automatically extracted from the control PLC's (Programmable Logic Controller)
Also, the time period over which a 24h period runs is not from midnight to midnight, but from 07h00 (am) to 07h00 (am)
Example: Unit one stops at 04h00 till 09h00 on the 20/04/2016. For the calculation to work, I need 04h00 till 07h00 to be captured on the 19/04/2016, and the time from 07h00 till 09h00 to be captured on 20/04/2016. There may be multiple stops in a 24h period.
Running hours is calculated as 24 - Sum(LostTime) on a specific date. In the above example it will be 24-3, i.e. 21 hours for the 19th, and 24-2=22h on the 20th
Reliability will then be calculated as (24-Sum(LostTime))/24 as a percentage. i.e. 21/24 = 87.5% on the 19th and 22/24 = 91.7% on the 20th
Total hours (run hours + lost hours) cannot be more than 24. and using straight forward DateDiff(StopDate, StartDate, hours) will not work as a stop can also be over a period longer than 24h ie the unit stops on Sunday and restart on Wednesday
The data is simple enough:
Unit StopDate/Time StartDate/Time TypeOfStoppage
Unit 1 2016/04/20 04:00:00 2016/04/20 09:00:00 Breakdown
Unit 1 2016/04/20 16:00:00 2016/04/20 19:00:00 Planned Stoppage
etc, etc
Thanks for providing a great explanation and raw data. Hopefully this will help you get to your solution. Using your data, you can create the following columns:
DayStart = DATEVALUE([StopDate/Time]) + TIME(7,0,0) RawHoursBefore = (HOUR(StopTimes[DayStart])-HOUR(StopTimes[StopDate/Time])) HoursBefore = IF(StopTimes[RawHoursBefore]>0,[RawHoursBefore],0) HoursAfter = IF(StopTimes[StopDate/Time]<StopTimes[DayStart],HOUR(StopTimes[StartDate/Time])-HOUR([DayStart]),HOUR(StopTimes[StartDate/Time]) - HOUR(StopTimes[StopDate/Time]))
Thanks a lot for the support. I will run it in the morning when I get back at the office, but I am a bit unsure of how I will use the results.
I assume from this I will get something like:
Unit StopDate/Time StartDate/Time TypeOfStop DayStart RawHoursBefore HoursBefore HoursAfter
Unit 1 20160420 04:00:00 20160420 09:00:00 Breakdown 20160420 07:00:00 3 3 2
This provide the immediate answer to what was before the cuttoff time of 07h00, and after 07h00, but what I now need to be able to sum the LostTime data for records before, i.e. with a StopDate/Time of 20160419 and that with a StopDate/Time of 20160420 AND an HoursBefore value > 0.
I am not sure if I explain it clearly enough, but the sum of all hours that fell between 07h00 on the 19th and 07h00 on the 20th becomes the lost time for the 19th, and that will then be used in measures/kpi's such as NAI, GAI, MTBF, MTBS, OEE etc (Net Availability Index, Gross Availability Index, Mean Time Between Failure, Mean Time Between Stops, Overall Equipment Efficiency) by using the Type Field.
The TYPES are essentially INCIDENTS, PLANNED, CIRCUMSTANTIAL
In Excel, I do it by creating a new table (per day) using SUMIFS to create a record per day that provides me with a table that looks as in the following example:
Unit Date Incident Circumstantial Planned NoOfStops NoOfFailures RunTime
Unit 1 19/04/2016 3 2 1 1 2 =24-sum(Inc, Circ, Plan) = 18
From this table I can create all the measures/kpi's mentioned above with the aid of an additional table Production providing the production per Unit per day, i.e.
Unit Date TonsProduced
Unit1 19/04/2016 1200
This in turn is related to a Unit Capacity Table, providing the design capacity of each unit, to allow calculation of Capacity Utilisation factor, which is used in OEE calculations (see OEE Calculation Method)
Unit Capacity
Unit1 1500
At the end I may end up with a Table that gives me:
Date Unit Parameter Value
19042016 Unit1 GOEE 98%
19042016 Unit1 NOEE 98%
19042016 Unit1 GAI 99%
19042016 Unit1 NAI 99%
19042016 Unit1 PF 99%
19042016 Unit1 MTBS 7
19042016 Unit1 MTBF 12
etc etc
I am not sure if the above table is better with the parameters as FIELDS instead of each as a record, but I foresee the above structure gives me flexibility to add more parameters without changing the table structure.
There is an added complexity in that should I create the table above instead of using MEASURES, that I will get errors in doing MTD, YTD etc type aggregation, and it may be better to just use the first table in this post with MEASURES for the KPI's
Regards
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |