Call Concurrency

Good afternoon. I am working with Fire and Emergency Medical Services response data for my dissertation. I am trying to determine 911 call concurrency. Concurrent calls are those that occur simultaneously. That is, when two or more 911 incidents are active within the same emergency medical services (EMS) response area at the same time. The attached sample shows the incident number, dispatch time, available time, and EMS Area.

Column "F" or "dispatch time" is the time of day when a specific truck was dispatched to an incident, which could also be considered the incident start time. Column "H" or "Available" time is when that same truck is back in service and no longer dedicated to the 911 incident, which could also be considered the incident stop time. It would be nice if an incident or line could be flagged if it occurred during the same time another incident was already in progress within the same "EMS Area" or Column "I."

An example of a concurrent incident would be line 11, where incident number 8000010 occurred while incident 8000007 was still taking place. Both were occurring at the same time within the "SP4" EMS area. I hope that makes sense.

The data set seems to contain "=" signs in front of the characters which is how report is generated. Thank you for your help.

Is there a minimum time between calls that defines concurrency?

Originally Posted by protonLeah

Is there a minimum time between calls that defines concurrency?

No, essentially it is when two or more incidents are active at the same time within the same EMS response area at the same time. The second, third, etc. incidents are labeled "concurrent" incidents.

Can you give an example of what you expect? With just this raw data and your description of what you're searching for, I don't see what you're after.

Example, if I look at EMS area SP8, there are 14 records. All but one happened on 01/01/2018, but all happened at a different time in column C. Can you guide us a little more in what you expect?

Originally Posted by jeffreybrown

Can you give an example of what you expect?

Yes, sorry. Column "F" or "dispatch time" is the time of day when a specific truck was dispatched to an incident. Column "H" or "Available" time is when that same truck is back in service and no longer dedicated to the 911 incident. It would be nice if an incident or line could be flagged if it occurred during the same time another incident was already in progress within the same "EMS Area" or Column "I." I hope that makes sense.

It makes sense, but could you point out one specific example!

Hello jrckt320. Welcome to the forum.

Unless I missed it I'm guessing at how you want these "flagged".
Please try in K2 and fill down.

Formula:

Please Login or Register  to view this content.

BTW: I included provision for trans-midnight alarms. There are 8 such incidents. There is only one that coincides with another alarm and that other truck was back in service before midnight. Having said that it would be easier if there were a second Date field to pair with the Available times.

another option

Please Login or Register  to view this content.

but
jrckt320 If 1st inc. is finished, 2nd is in progress and 3rd just start what result should be? Or doesn't it meter.

Originally Posted by jrckt320

Dave, your formula seems to do the trick. Thank you. Question, Is there a way to edit the formula so it also "tags" the initial incident as well? For instance, IF there is a concurrent incident, incident 2 would be labeled as such and incident 1 would also be labeled? That way I could identify the "set" of incidents within the same response zone that happened at the same time.

This is my 3rd edit/rewrite.
Given the data and layout you have I am skeptical. With each incident/company/area laid out as one record I don't think so. Or at least it will be very awkward.

On the other hand if you have data feed recorded in real time line then maybe yes. Even then you may have to take this one to commercial services.

Originally Posted by FlameRetired

This is my 3rd edit/rewrite.
Given the data and layout you have I am skeptical. With each incident/company/area laid out as one record I don't think so. Or at least it will be very awkward.

On the other hand if you have data feed recorded in real time line then maybe yes. Even then you may have to take this one to commercial services.

I appreciate all of your help. I tried to "fill down" the formula to all 200K lines of data. Excel presented me with the "this will probably take a while" warning. It's been running for about 8 hours now. I'm not sure if I should let it continue or just force quit excel. (See attached)

Screen Shot 2020-02-27 at 5.18.31 AM.png

With that much output I am not surprised. That is an array formula. They are resource hungry.

If that lag is to be overcome it will require another approach. Are helper columns (maybe several) permissible?

BTW: I failed to mention earlier that your data is all text "dates" and "times". If you are not aware of it dates and times are numbers. Dates are number of days elapsed starting on 1/1/1900. The formatting is cosmetic. Times are decimal "fractions" of a day. 8/24=1/3=8:00 AM, 12/24=1/2=12:00 Noon, 16/24=2/3=4:00 PM, etc. etc. The formatting is again cosmetic.

This fact is what makes date and time math doable. This also means the text "date/times" must be coerced into their underlying numeric values in order to be useful. Applied to math operations ... +, *, /, ^ or - has that affect as does preceding the text "numbers" with the double unary --. However it is best to have source data whose data type is numeric to start with. What additional load the conversions of the text dates and times by coercion within the formulas have on performance (if any) I can not say. I've never tested this. But having source data types already numeric almost always saves confusion when building summaries like this. That becomes more apparent when comparing or matching those because until converted the numeric values of text are all 0.

FlameRetired,

Sure, helper columns aren't an issue. Right now I am unable to analyze all of the data I have because Excel just freezes. Thanks

Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

Originally Posted by jrckt320

Sure, helper columns aren't an issue. Right now I am unable to analyze all of the data I have because Excel just freezes.

Even with helper columns I only managed to make a formula that takes @ 2/3 the time it used to. Given the times you are seeing that is not even close to enough.

Edit Let me take this back to the community. I don't think a formula solution it going to do what you want. Since I do not know enough VBA I can not say if a more efficient solution can be done that way.

One of the community Gurus suggested I post what I came up with just the same. Here is what I've done. First the text dates and times have been all converted to numeric data types.

Then in columns P and R the combined dates and times with date compensated when crossing mid nights. Those formulas are

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The old formula is in column K. This new one in column L.

Formula:

Please Login or Register  to view this content.

Not sure how much difference it will make, but with the new set up the SUMPRODUCT based formula in L doesn't need to be array entered.
I suggest that jrckt320 try applying the SUMPRODUCT formula (just press the enter key to activate) along with the two helper columns to the actual data and see if that helps.
Let us know if you have any questions.

jrckt320

Please find incident # "8000194" (row 195). What EMS Area should that be in?

In the meantime I assigned that incident a fictitious EMS Area of "ZZZZZZ".

This formula is considerably faster than the other two. Whether it will be suitably faster on your live data or not remains to be seen.

Formula:

Please Login or Register  to view this content.