number of records within a start and endtime

[chvizda]

Hi all

I have following problem and hope someone can help me to solve it:

I have a dataset (up to 100K rows) with phone calls. I have a unique call ID and a start time and a endime of the call and also the duration in seconds.

Now I need to find out what is the number of concurrent calls within the time frames.

I don't know how to make this because of the overlap of the calls. 

It would be enough to have the result per minute. I tried it with some formulas to get calls with the same connect time and Disconnect time but I didn't have the overlaps.

I hope you can help me to solve this.

Many thanks

Steffen 

IR_CALLID 6487398 871413 6487305 9700434 6487402 6487406 6487386 871284 871086 6487180 6487199 871272 871285 871279 871271 871208 6487182 6487185 6487184 871210 6487186 871283 6487197 6487183 6487181 871207 871287 871286 9700422

IR_CONNTIME_String 14 Jun 2022 11:13:03 14 Jun 2022 11:12:40 14 Jun 2022 11:09:13 14 Jun 2022 11:08:01 14 Jun 2022 11:13:58 14 Jun 2022 11:14:28 14 Jun 2022 11:12:41 14 Jun 2022 11:04:20 14 Jun 2022 10:56:07 14 Jun 2022 11:03:49 14 Jun 2022 11:04:43 14 Jun 2022 11:03:37 14 Jun 2022 11:04:20 14 Jun 2022 11:04:12 14 Jun 2022 11:03:37 14 Jun 2022 11:00:18 14 Jun 2022 11:03:51 14 Jun 2022 11:03:56 14 Jun 2022 11:03:56 14 Jun 2022 11:00:18 14 Jun 2022 11:03:56 14 Jun 2022 11:04:20 14 Jun 2022 11:04:37 14 Jun 2022 11:03:56 14 Jun 2022 11:03:51 14 Jun 2022 11:00:31 14 Jun 2022 11:04:25 14 Jun 2022 11:04:20 14 Jun 2022 11:06:32

IR_DISCTIME_String 14 Jun 2022 11:14:23 14 Jun 2022 11:14:34 14 Jun 2022 11:14:03 14 Jun 2022 11:14:26 14 Jun 2022 11:14:29 14 Jun 2022 11:14:31 14 Jun 2022 11:14:31 14 Jun 2022 11:04:22 14 Jun 2022 11:03:53 14 Jun 2022 11:04:19 14 Jun 2022 11:04:44 14 Jun 2022 11:03:49 14 Jun 2022 11:04:22 14 Jun 2022 11:04:13 14 Jun 2022 11:03:49 14 Jun 2022 11:03:49 14 Jun 2022 11:03:56 14 Jun 2022 11:04:28 14 Jun 2022 11:04:28 14 Jun 2022 11:03:49 14 Jun 2022 11:04:28 14 Jun 2022 11:04:22 14 Jun 2022 11:04:38 14 Jun 2022 11:04:28 14 Jun 2022 11:03:56 14 Jun 2022 11:03:49 14 Jun 2022 11:04:26 14 Jun 2022 11:04:22 14 Jun 2022 11:14:25

IR_DURATION 80 114 290 385 31 3 110 2 466 30 1 12 2 1 12 211 5 32 32 211 32 2 1 32 5 198 1 2 473

IR_OLOCATN L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_SG L_VAD L_VAD L_VAD L_SG L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD

IR_DLOCATN L_ZH L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_SG L_VAD L_VAD L_VAD L_VAD L_VAD L_SG L_VAD L_VAD L_VAD L_SG L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD L_VAD

[Qiu]

@chvizda 
I had to check what is the definition for "concurrent calls" and based on below. I came up this sample flow.
You did mention that you have rather huge data stream, and using Generate Rows is not so wise, but I dont see there is another options.
Hope others can give a better solution.

https://www.lawinsider.com/dictionary/concurrent-call

I will first convert the Call Start and End time into DateTime format, then generate rows by adding 1 minutes since you said it is enough to have the result per minute.

Then we use DateTime tool to remove the seconds then we have a series of Time Stamp in minutes where the calling is on-going.

Then a Summarize Tool will do the rest.

[Matt_D]

Hi @chvizda you need to be careful with this, I spent many (too many) years working in workforce planning so have dealt with contact volumes more times than I'd like to admit.

You mentioned the word "concurrent calls", the max concurrent calls in your data is 8 over a period between 11:04:20 and 11:04:22.

If you're looking at showing this at a minute level, you maybe better showing the MAX concurrent calls within that minute / interval.

Just one caveat, generating "seconds" over up to 100K rows will generate a huge dataset.

[Qiu]

@Matt_D 
Allow me to have one comment.
I think essentially you are picking the max number calls per second for each minute.

That is why our results are having difference.