Advanced cross tab calculation

DM_88

Hi all

I want to do a cross tab calculation but I don't know were to start.

The thing that I want to do is transform next data:

ID	Start	Stop
A	6/2/2021 0:00:02	6/2/2021 0:07:32
B	6/2/2021 0:00:04	6/2/2021 0:01:17
C	6/2/2021 0:00:05	6/2/2021 0:00:14
D	6/2/2021 0:00:05	6/2/2021 0:01:21
E	6/2/2021 0:00:06	6/2/2021 0:01:48
F	6/2/2021 0:00:07	6/2/2021 0:03:19
G	6/2/2021 0:00:08	6/2/2021 0:00:10
H	6/2/2021 0:00:09	6/2/2021 0:03:09
I	6/2/2021 0:00:10	6/2/2021 0:00:15
J	6/2/2021 0:00:10	6/2/2021 0:03:07
K	6/2/2021 0:00:11	6/2/2021 0:00:16
L	6/2/2021 0:00:14	6/2/2021 0:00:16

Into this:

			Timeline
ID	Start	Stop	6/2/2021 0:00:00	6/2/2021 0:00:01	6/2/2021 0:00:02	6/2/2021 0:00:03	6/2/2021 0:00:04	6/2/2021 0:00:05	6/2/2021 0:00:05	6/2/2021 0:00:06	6/2/2021 0:00:07	6/2/2021 0:00:08	6/2/2021 0:00:09	6/2/2021 0:00:10	6/2/2021 0:00:10	6/2/2021 0:00:11	6/2/2021 0:00:12	6/2/2021 0:00:13	6/2/2021 0:00:14	6/2/2021 0:00:15	6/2/2021 0:00:16	6/2/2021 0:00:17
A	6/2/2021 0:00:02	6/2/2021 0:07:32			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
B	6/2/2021 0:00:04	6/2/2021 0:01:17					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
C	6/2/2021 0:00:05	6/2/2021 0:00:14						1	1	1	1	1	1	1	1	1	1	1	1
D	6/2/2021 0:00:05	6/2/2021 0:01:21						1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
E	6/2/2021 0:00:06	6/2/2021 0:01:48								1	1	1	1	1	1	1	1	1	1	1	1	1
F	6/2/2021 0:00:07	6/2/2021 0:03:19									1	1	1	1	1	1	1	1	1	1	1	1
G	6/2/2021 0:00:08	6/2/2021 0:00:10										1	1	1	1
H	6/2/2021 0:00:09	6/2/2021 0:03:09											1	1	1	1	1	1	1	1	1	1
I	6/2/2021 0:00:10	6/2/2021 0:00:15												1	1	1	1	1	1	1
J	6/2/2021 0:00:10	6/2/2021 0:03:07												1	1	1	1	1	1	1	1	1
K	6/2/2021 0:00:11	6/2/2021 0:00:16														1	1	1	1	1	1
L	6/2/2021 0:00:14	6/2/2021 0:00:16																	1	1	1
			0	0	1	1	2	4	4	5	6	7	8	10	10	10	10	10	11	10	9	7

So at the end I want to see how many ID's per second were active.

Do's anyone of you have the solution?

 Thanks a lot!

danilang

hi @DM_88 

Here's a non macro solution.

The top branch generates all the seconds that each item is active and then groups by second to find the count for each second.  The bottom one generates all possible seconds between the start and the end to fill in any missing gaps.

This workflow outputs the list of seconds as rows, because it will be much easier to deal with.   If you require the output in columns, add a crosstab at the end

Dan 

ActiveInEachSecond.yxmd