Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all
I am trying to put together some MI on SLAs based on the time between certain steps. The difficulty is that account, step and start time are all columns in the same table. Can anyone advise how I can summarise turnaround times between certain steps on a case by case basis?
Sample data and output required shown below.
It would also be great to base this only on working hours, and get some % hit and miss based on expected turnaround times but not sure if this is a step too far!
Thanks in advance
Sample Data | ||
Case | Step | DateTime |
Case 1 | Step A | 01/04/2020 08:00 |
Case 1 | Step B | 01/04/2020 09:45 |
Case 1 | Step C | 01/04/2020 11:45 |
Case 1 | Step D | 02/04/2020 15:00 |
Case 1 | Step E | 02/04/2020 16:40 |
Case 1 | Step F | 03/04/2020 09:00 |
Case 2 | Step A | 01/04/2020 10:24 |
Case 2 | Step B | 01/04/2020 12:09 |
Case 2 | Step C | 01/04/2020 14:09 |
Case 2 | Step D | 02/04/2020 17:24 |
Case 2 | Step E | 02/04/2020 19:04 |
Case 2 | Step F | 03/04/2020 11:24 |
Case 3 | Step A | 01/04/2020 12:48 |
Case 3 | Step B | 01/04/2020 14:33 |
Case 3 | Step C | 01/04/2020 16:33 |
Case 3 | Step D | 02/04/2020 19:48 |
Case 3 | Step E | 02/04/2020 21:28 |
Case 3 | Step F | 03/04/2020 13:48 |
Case 4 | Step A | 01/04/2020 15:12 |
Case 4 | Step B | 01/04/2020 16:57 |
Case 4 | Step C | 01/04/2020 18:57 |
Case 4 | Step D | 02/04/2020 22:12 |
Case 4 | Step E | 02/04/2020 23:52 |
Case 4 | Step F | 03/04/2020 16:12 |
Case 5 | Step A | 01/04/2020 17:36 |
Case 5 | Step B | 01/04/2020 19:21 |
Case 5 | Step C | 01/04/2020 21:21 |
Case 5 | Step D | 03/04/2020 00:36 |
Case 5 | Step E | 03/04/2020 02:16 |
Case 5 | Step F | 03/04/2020 18:36 |
Case 6 | Step A | 01/04/2020 20:00 |
Case 6 | Step B | 01/04/2020 21:45 |
Case 6 | Step C | 01/04/2020 23:45 |
Case 6 | Step D | 03/04/2020 03:00 |
Case 6 | Step E | 03/04/2020 04:40 |
Case 6 | Step F | 03/04/2020 21:00 |
Sample Output | ||||
Step A-B | Step C-D | Step D-E | Step E-F | |
Case 1 | 1:45 | 3:15 | 1:40 | 16:20 |
Case 2 | 1:45 | 3:15 | 1:40 | 16:20 |
Case 3 | 1:45 | 3:15 | 1:40 | 16:20 |
Case 4 | 1:45 | 3:15 | 1:40 | 16:20 |
Case 5 | 1:45 | 3:15 | 1:40 | 16:20 |
Case 6 | 1:45 | 3:15 | 1:40 | 16:20 |
Solved! Go to Solution.
Use the pivot function with Case and Stage highlighted:
Then you calculate the differences in time with new columns!
Br,
J
Hi @JPrince_22 ,
Is the maximum amount of steps defined or can there occur cases in the future with more steps than there is now?
Br,
J
Hi @tex628
There are 48 steps in total however only 7 of them are of interest. New cases continuously cycle through the steps so new cases will be added all the time, but the steps themselves will remain stable.
Thanks
Use the pivot function with Case and Stage highlighted:
Then you calculate the differences in time with new columns!
Br,
J