Introduction
You have been hired by a Coal Terminal to assess which of their Coal Reclaimer machines require maintenance in the upcoming month.
The machines run literally round the clock 24/7 for 365 days a year. Every minute of downtime equates to millions of dollars lost revenue, that is why it is crucial to identify exactly when these machines require maintenance (neither less or more frequently is acceptable).
Currently the Coal Terminal follows the following criterion: a reclaimer-type machine requires maintenance when within the previous month there was at least one 8-hour period when the average idle capacity was over 10%.
Idle Capacity is a utilization metric which, for the purposes of this project, is defined as:
Idle capacity = (Actual Tonnage - Nominal Capacity) / Nominal Capacity
Your task is to find out which of the 5 machines have exceeded this level and create a report for the executive stakeholders with your recommendations.
The Data
RL1:
| Datetime | Nominal Capacity | Actual Tonnes |
|---|---|---|
| 01 Sep 2015 00:00 | 4200 | 4200 |
| 01 Sep 2015 01:00 | 4200 | 3941 |
| 01 Sep 2015 02:00 | 4200 | 3950 |
| 01 Sep 2015 03:00 | 4200 | 4200 |
| 01 Sep 2015 04:00 | 4200 | 4041 |
| 01 Sep 2015 05:00 | 4200 | 4200 |
RL2:
| Datetime | Nominal Capacity | Actual Tonnes |
|---|---|---|
| 01 Sep 2015 00:00 | 3350 | 3350 |
| 01 Sep 2015 01:00 | 3350 | 3350 |
| 01 Sep 2015 02:00 | 3350 | 3350 |
| 01 Sep 2015 03:00 | 3350 | 3350 |
| 01 Sep 2015 04:00 | 3350 | 3350 |
| 01 Sep 2015 05:00 | 3350 | 3350 |
| 01 Sep 2015 06:00 | 3350 | 3228 |
| 01 Sep 2015 07:00 | 3350 | 3337 |
SR1:
| Datetime | Nominal Capacity | Actual Tonnes |
|---|---|---|
| 01 Sep 2015 00:00 | 4500 | 4339 |
| 01 Sep 2015 01:00 | 4500 | 4179 |
| 01 Sep 2015 02:00 | 4500 | 4262 |
| 01 Sep 2015 03:00 | 4500 | 3779 |
| 01 Sep 2015 04:00 | 4500 | 3871 |
| 01 Sep 2015 05:00 | 4500 | 4500 |
| 01 Sep 2015 06:00 | 4500 | 4423 |
| 01 Sep 2015 07:00 | 4500 | 3960 |
| 01 Sep 2015 08:00 | 4500 | 4500 |
| 01 Sep 2015 09:00 | 4500 | 4500 |
SR4A:
| Datetime | Nominal Capacity | Actual Tonnes |
|---|---|---|
| 06 Sep 2015 00:00 | 4500 | 3993 |
| 06 Sep 2015 01:00 | 4500 | 4500 |
| 06 Sep 2015 02:00 | 4500 | 4499 |
| 06 Sep 2015 03:00 | 4500 | 4370 |
| 06 Sep 2015 04:00 | 4500 | 4499 |
| 06 Sep 2015 05:00 | 4500 | 4500 |
| 06 Sep 2015 06:00 | 4500 | 4500 |
| 06 Sep 2015 07:00 | 4500 | 4497 |
| 06 Sep 2015 08:00 | 4500 | 4244 |
| 06 Sep 2015 09:00 | 4500 | 4497 |
| 06 Sep 2015 10:00 | 4500 | 3770 |
| 06 Sep 2015 11:00 | 4500 | 4438 |
SR6:
| Datetime | Nominal Capacity | Actual Tonnes |
|---|---|---|
| 01 Sep 2015 00:00 | 3450 | 3365 |
| 01 Sep 2015 01:00 | 3450 | 3124 |
| 01 Sep 2015 02:00 | 3450 | 2978 |
| 01 Sep 2015 03:00 | 3450 | 3450 |
| 01 Sep 2015 04:00 | 3450 | 2859 |
| 01 Sep 2015 05:00 | 3450 | 2939 |
| 01 Sep 2015 06:00 | 3450 | 3450 |
| 01 Sep 2015 07:00 | 3450 | 3450 |
| 01 Sep 2015 08:00 | 3450 | 3349 |
| 01 Sep 2015 09:00 | 3450 | 2991 |
Visualization
Explain:
why there are some gaps in SR1,SR4A,SR6? there are 312 nulls? what do they mean?
Basically, RL1 and RL2 have no gaps, because they are declaimers. What they always do is they collect coal and then they send it off to the ships.
SR stands for stacker reclaimer, sometimes stackers are as told to stack coal, sometimes stackers are called to reclaim call. This machine starts from the start of the month,all the way up until somewhere around 13th of September 13, it was reclaiming coal, then it was told to go and stack coal. it was put on a different job that is why we don’t have any data during this period, because we only analyzing the utilization of reclaimers.
Demo:
Check the full-version:
Notes
In this section:
we learned:
- create multiple joins
- calculate fields and table calculations
- creating advanced table calculations
- add second layer moving average
- trendlines for power insights
- create a storyline
