+ Reply to Thread
Results 1 to 21 of 21

Call Concurrency

  1. #1
    Registered User
    Join Date
    02-21-2020
    Location
    Clearwater, FL
    MS-Off Ver
    365
    Posts
    8

    Arrow 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.
    Attached Files Attached Files
    Last edited by jrckt320; 02-23-2020 at 07:38 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,203

    Re: Call Concurrency

    Is there a minimum time between calls that defines concurrency?
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    02-21-2020
    Location
    Clearwater, FL
    MS-Off Ver
    365
    Posts
    8

    Post Re: Call Concurrency

    Quote Originally Posted by protonLeah View Post
    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.
    Last edited by jrckt320; 02-23-2020 at 06:54 PM.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    Re: Call Concurrency

    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?
    HTH
    Regards, Jeff

  5. #5
    Registered User
    Join Date
    02-21-2020
    Location
    Clearwater, FL
    MS-Off Ver
    365
    Posts
    8

    Re: Call Concurrency

    Quote Originally Posted by jeffreybrown View Post
    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.
    Last edited by jeffreybrown; 02-23-2020 at 07:18 PM. Reason: Please try to avoid using full quotes!

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    Re: Call Concurrency

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

  7. #7
    Registered User
    Join Date
    02-21-2020
    Location
    Clearwater, FL
    MS-Off Ver
    365
    Posts
    8

    Re: Call Concurrency

    Quote Originally Posted by jeffreybrown View Post
    It makes sense, but could you point out one specific example!
    Sure, an example of a concurrent incident would be line 11, where incident number 8000010 occurred while incident 8000007 was still open. Both were occurring at the same time within the SP4 EMS area.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Call Concurrency

    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: copy to clipboard
    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.
    Last edited by FlameRetired; 02-23-2020 at 09:26 PM.
    Dave

  9. #9
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,234

    Re: Call Concurrency

    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.
    Last edited by BMV; 02-24-2020 at 02:26 AM.

  10. #10
    Registered User
    Join Date
    02-21-2020
    Location
    Clearwater, FL
    MS-Off Ver
    365
    Posts
    8

    Re: Call Concurrency

    Quote Originally Posted by FlameRetired View Post
    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: copy to clipboard
    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.
    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.

  11. #11
    Registered User
    Join Date
    02-21-2020
    Location
    Clearwater, FL
    MS-Off Ver
    365
    Posts
    8

    Re: Call Concurrency

    Quote Originally Posted by BMV View Post
    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.
    Yes, in that case incident number 3 would also be considered concurrent if it happened while Incident 2 is still open.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Call Concurrency

    Quote Originally Posted by jrckt320 View Post
    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.
    Last edited by FlameRetired; 02-26-2020 at 05:50 PM.

  13. #13
    Registered User
    Join Date
    02-21-2020
    Location
    Clearwater, FL
    MS-Off Ver
    365
    Posts
    8

    Arrow Re: Call Concurrency

    Quote Originally Posted by FlameRetired View Post
    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
    Last edited by jrckt320; 02-27-2020 at 06:22 AM.

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Call Concurrency

    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.
    Last edited by FlameRetired; 02-28-2020 at 03:23 PM.

  15. #15
    Registered User
    Join Date
    02-21-2020
    Location
    Clearwater, FL
    MS-Off Ver
    365
    Posts
    8

    Re: Call Concurrency

    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
    Last edited by jeffreybrown; 02-29-2020 at 02:03 PM. Reason: Please try to avoid using full quotes!

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    Re: Call Concurrency

    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

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Call Concurrency

    Quote Originally Posted by jrckt320 View Post
    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.
    Last edited by FlameRetired; 03-01-2020 at 07:16 PM.

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Call Concurrency

    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: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The old formula is in column K. This new one in column L.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,205

    Re: Call Concurrency

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  20. #20
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Call Concurrency

    jrckt320

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

  21. #21
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,630

    Re: Call Concurrency

    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: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Repeat call count for call center
    By arifmasum in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2013, 10:03 PM
  2. How to set call count according to call date and time
    By naveen4pundir in forum Access Tables & Databases
    Replies: 0
    Last Post: 04-27-2012, 02:13 AM
  3. How to avoid concurrency in Access database when using Excel as Frontend
    By RagonichaFulva in forum Access Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2012, 08:56 AM
  4. Call Sub
    By bishoposiris in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2009, 03:40 AM
  5. using CALL to call methods mandatory?
    By jerryliang2k in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2008, 06:16 PM
  6. How To Call It
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 06-03-2008, 11:59 PM
  7. [SOLVED] So odd, not sure what to call it!
    By mtonkovich@msn.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-03-2006, 01:15 PM
  8. [SOLVED] Call Center Management: How to calculate 'cost per call'
    By Denniso6 in forum Excel General
    Replies: 2
    Last Post: 06-25-2006, 12:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1