Hello everyone
I'm quite stucked since several days on the following issue and i can't figure out how to manage this.
First table is just a bunch of ID with start date & end date. For one ID, i may have several data range which are representing the time when the incident was in a specific group.
The second Table is the same bunch of ID with some range as well, which are representing the time when the incident was in a specific state. ("in progress")
My main goal is to be able retrieve the total volume of "in progress" in the resolver group used for the first table during working hours days.
For the story of working hours, i've been able to sort it out..
But i'm unable to successfully cross the information between both tables to build the intersection between all data range for a same incident. As i could have several lines & uses cases for the overlapping period between data ranges, i'm able to retrieve some information (such as the start date & end date of the presence in the resolver group if there is an overlapping period with the "in progress" table using Index(Match(1; Condition * condition...)) but as soon as i have several lines in my progress table that are fitting in the same data range of my first table (see ID 14216 for instance), i'm totally stucked.
If someone of you has a crazy idea, i take it
Thanks !
I'm quite stucked since several days on the following issue and i can't figure out how to manage this.
First table is just a bunch of ID with start date & end date. For one ID, i may have several data range which are representing the time when the incident was in a specific group.
The second Table is the same bunch of ID with some range as well, which are representing the time when the incident was in a specific state. ("in progress")
My main goal is to be able retrieve the total volume of "in progress" in the resolver group used for the first table during working hours days.
For the story of working hours, i've been able to sort it out..
But i'm unable to successfully cross the information between both tables to build the intersection between all data range for a same incident. As i could have several lines & uses cases for the overlapping period between data ranges, i'm able to retrieve some information (such as the start date & end date of the presence in the resolver group if there is an overlapping period with the "in progress" table using Index(Match(1; Condition * condition...)) but as soon as i have several lines in my progress table that are fitting in the same data range of my first table (see ID 14216 for instance), i'm totally stucked.
If someone of you has a crazy idea, i take it
Thanks !| incident_metric (4).xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | ID | Start Date | End Date | ||
| 2 | 00465 | 29/07/2021 14:17 | 10/08/2021 13:02 | ||
| 3 | 00465 | 21/07/2021 14:25 | 22/07/2021 16:13 | ||
| 4 | 00508 | 21/07/2021 12:52 | 09/08/2021 02:03 | ||
| 5 | 02862 | 23/07/2021 15:23 | 27/07/2021 18:36 | ||
| 6 | 02862 | 22/07/2021 08:47 | 23/07/2021 10:05 | ||
| 7 | 02862 | 29/07/2021 13:41 | 10/08/2021 13:04 | ||
| 8 | 03433 | 27/07/2021 09:53 | 10/08/2021 12:02 | ||
| 9 | 03433 | 22/07/2021 10:33 | 23/07/2021 10:04 | ||
| 10 | 04328 | 23/07/2021 14:01 | 05/08/2021 17:00 | ||
| 11 | 05781 | 04/08/2021 14:32 | 11/08/2021 17:06 | ||
| 12 | 06865 | 28/07/2021 12:44 | 10/08/2021 15:05 | ||
| 13 | 06865 | 23/07/2021 16:34 | 26/07/2021 15:22 | ||
| 14 | 07315 | 23/07/2021 09:50 | 03/08/2021 19:00 | ||
| 15 | 07662 | 23/07/2021 10:44 | 14/08/2021 02:00 | ||
| 16 | 08119 | 27/07/2021 11:31 | 04/08/2021 12:04 | ||
| 17 | 10803 | 29/07/2021 15:39 | 30/07/2021 09:37 | ||
| 18 | 10803 | 02/08/2021 07:22 | 11/08/2021 11:10 | ||
| 19 | 10803 | 26/07/2021 08:17 | 29/07/2021 10:37 | ||
| 20 | 11678 | 29/07/2021 11:52 | 23/08/2021 12:09 | ||
| 21 | 11678 | 26/07/2021 10:05 | 28/07/2021 15:38 | ||
| 22 | 12329 | 06/08/2021 14:39 | 13/08/2021 17:02 | ||
| 23 | 13037 | 28/07/2021 09:27 | 20/08/2021 13:03 | ||
| 24 | 13037 | 26/07/2021 14:56 | 27/07/2021 19:02 | ||
| 25 | 14216 | 27/07/2021 09:57 | 30/07/2021 14:40 | ||
| 26 | 14216 | 13/08/2021 07:34 | 13/08/2021 13:47 | ||
| 27 | 14216 | 17/08/2021 10:46 | 24/08/2021 16:06 | ||
| 28 | 14216 | 05/08/2021 11:04 | 09/08/2021 15:45 | ||
Duration in RG | |||||
| incident_metric (4).xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | ID | Start Date | End Date | ||
| 2 | 00465 | 30/07/2021 16:30 | 30/07/2021 17:29 | ||
| 3 | 00465 | 28/07/2021 13:54 | 30/07/2021 09:53 | ||
| 4 | 00465 | 28/07/2021 13:35 | 28/07/2021 13:48 | ||
| 5 | 00465 | 21/07/2021 14:23 | 21/07/2021 16:27 | ||
| 6 | 00465 | 22/07/2021 14:10 | 23/07/2021 13:18 | ||
| 7 | 00465 | 21/07/2021 17:35 | 22/07/2021 09:17 | ||
| 8 | 00508 | 21/07/2021 12:49 | 21/07/2021 15:11 | ||
| 9 | 02862 | 23/07/2021 15:23 | 30/07/2021 12:51 | ||
| 10 | 02862 | 30/07/2021 16:30 | 30/07/2021 17:29 | ||
| 11 | 02862 | 22/07/2021 08:44 | 23/07/2021 14:30 | ||
| 12 | 03433 | 22/07/2021 10:12 | 23/07/2021 16:18 | ||
| 13 | 03433 | 27/07/2021 09:43 | 30/07/2021 13:52 | ||
| 14 | 03433 | 30/07/2021 16:30 | 30/07/2021 17:28 | ||
| 15 | 04328 | 29/07/2021 15:55 | 29/07/2021 16:10 | ||
| 16 | 04328 | 22/07/2021 12:21 | 29/07/2021 14:24 | ||
| 17 | 05781 | 22/07/2021 16:13 | 23/07/2021 14:24 | ||
| 18 | 05781 | 04/08/2021 14:32 | 04/08/2021 16:51 | ||
| 19 | 06865 | 23/07/2021 11:47 | 28/07/2021 10:12 | ||
| 20 | 06865 | 28/07/2021 10:43 | 30/07/2021 12:51 | ||
| 21 | 06865 | 30/07/2021 16:30 | 30/07/2021 17:29 | ||
| 22 | 06865 | 23/07/2021 08:33 | 23/07/2021 08:34 | ||
| 23 | 07315 | 23/07/2021 09:47 | 27/07/2021 18:15 | ||
| 24 | 07662 | 23/07/2021 10:26 | 23/07/2021 12:32 | ||
| 25 | 07662 | 26/07/2021 10:16 | 26/07/2021 10:38 | ||
| 26 | 07662 | 23/07/2021 14:39 | 26/07/2021 09:22 | ||
| 27 | 08119 | 27/07/2021 08:50 | 27/07/2021 10:55 | ||
| 28 | 08119 | 27/07/2021 11:31 | 28/07/2021 11:00 | ||
| 29 | 08119 | 23/07/2021 11:24 | 26/07/2021 21:39 | ||
| 30 | 10803 | 02/08/2021 07:22 | 02/08/2021 12:54 | ||
| 31 | 10803 | 29/07/2021 15:11 | 30/07/2021 14:46 | ||
| 32 | 10803 | 26/07/2021 08:05 | 29/07/2021 13:59 | ||
| 33 | 11678 | 29/07/2021 11:52 | 06/08/2021 16:29 | ||
| 34 | 11678 | 26/07/2021 09:55 | 28/07/2021 16:37 | ||
| 35 | 12329 | 26/07/2021 11:37 | 26/07/2021 19:08 | ||
| 36 | 12329 | 03/08/2021 14:02 | 05/08/2021 09:49 | ||
| 37 | 12329 | 27/07/2021 10:08 | 27/07/2021 11:53 | ||
| 38 | 12329 | 06/08/2021 13:47 | 06/08/2021 16:08 | ||
| 39 | 13037 | 26/07/2021 14:50 | 28/07/2021 07:26 | ||
| 40 | 13037 | 28/07/2021 09:27 | 03/08/2021 14:03 | ||
| 41 | 14216 | 26/07/2021 16:46 | 26/07/2021 16:47 | ||
| 42 | 14216 | 27/07/2021 08:52 | 30/07/2021 17:25 | ||
| 43 | 14216 | 03/08/2021 07:58 | 03/08/2021 15:08 | ||
| 44 | 14216 | 05/08/2021 09:46 | 05/08/2021 13:01 | ||
| 45 | 14216 | 06/08/2021 11:55 | 06/08/2021 15:17 | ||
| 46 | 14216 | 09/08/2021 11:27 | 16/08/2021 11:34 | ||
| 47 | 14216 | 17/08/2021 09:05 | 17/08/2021 15:46 | ||
In Progress | |||||
