Maxed out chats

Summary

Report creation for resource allocation

Content

We are working on resource allocation KPIs and attempting to build a maxed out chats report. To the business, "maxed out" means an agent is handling 3 chats at one time. We want to see a report that shows when an agent was handling more than one chat at a time, the number of chats they were handling at once, and the duration (minutes) those chats were overlapping. A mockup of this report is in the attached file, column J-M, blue header:

 

Also in the attached file, we have data that we can get from the system (columns A-F). By looking at requested time stamp (E) and completed time stamp (F) we can tell when chats are overlapping. Columns G & H are manually added for the purpose of illustrating what we are trying to achieve.