Call Avoidance Tracking: Column A contains any instance where Column B is +/- 1

[StandardSanders]

I am trying to track what is essentially call dodging in my call center. To do this I am looking for any instance where +/- 1 minute from when a call comes in to our queue one of the agents goes to an unavailable state.

Here is what data I have

Table 1 Inbound Calls:
Agent Name/Time

Table 2 Agent State:
Agent Name/State/Time

The way the data comes out of our program it is split between day/month/hour/minute so I have concatenated the four of these so it comes out as 330651 for a timestamp of March 30th at 6:51 AM. What I need is a way to simply count/track when that The time fields are within 1 minute plus or minus of each other and give me a count of occurrences by agent.

Thanks

 

[theBardd]

Can you post some data examples and expected results from same?

 

[StandardSanders]

Table 1 (Outbound State)
Agent_Name	Start_Date	Month	Day	Hour	Min	Time
Bob	2/31/2015 23:52	2	1	23	15	212315
Dave	2/5/2015 0:06	2	5	9	29	25929
Bob	2/5/2015 0:06	2	5	9	29	25929
Gail	2/5/2015 0:26	2	5	9	30	25930
Jenny	2/5/2015 0:28	2	5	9	30	25930
Steve	2/5/2015 0:30	2	5	9	30	25930
Gail	2/5/2015 0:34	2	5	9	31	25931
Table 2 (Inbound Call)
Agent_Name	Start_Date	start_time	Month	Day	Hour	Min	Time
Bob	2/31/2015	2:19:17	2	1	23	15	212315
N/A	2/1/2015	4:47:45	2	1	4	47	21447
Steve	2/1/2015	4:52:53	2	1	4	52	21452
Gail	2/1/2015	4:57:53	2	1	4	57	21457
Gail	2/1/2015	5:00:29	2	1	5	0	2150
Steve	2/1/2015	7:18:42	2	1	7	18	21718
Gail	2/1/2015	7:18:59	2	1	7	18	21718
Danny	2/1/2015	7:37:54	2	1	7	37	21737

<colgroup><col><col><col><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

Above are examples of the two tables I am looking to compare. Each has 1000s of entries but this is just a basic example. What I want to do is on the one marked outbound state to have a column marked with a Y/N or 1/0 if the Time field is exactly the same as any of the entries in the Time field of the 2nd table (or +/- 1 from it), otherwise it'd be a 0. In the above example only the 1st entry would be a "Y".

 

[scottsen]

I'm torn. The geek in my just ... loves this use of power pivot to find really cool data. But like, those poor slobs gonna get the big brother smack down!

First, I don't think I like your columns. I would keep it as just a date column and time column, and do math based on that. 1 = 1 day. 1 / (24 * 60) = 1 minute. As you have it... the plus or minute 1 minute is going to be pretty hard I think.

I can't decide which table... not sure it matters, but we are going to add a calc column that says "for this datetime... is there a corresponding datetime in the OTHER table for this same agent?".

Let's pretend we are doing this on the OutboundState table, cuz I have a slight preference for that location

Ack! I need to run, but I will come update this later... but at a hand-wavy level it is like...

HasMatchingCall := CALCULATE(COUNTROWS(InboundCall), FILTER(InboundCall, InboundCall[AgentName] = OutboundState[AgentName] && InboundCall[TheTime] = OutboundCall[TheTime])

Of course, "the time" isn't real... I would use DATEVALUE([Date]) + TIMEVALUE([Time]) to combine the columsn to one 1 thing. And you should subtract those and ... see if its between -1 and 1 ... or something

 

[ImkeF]

Hey, that looks good!

Now buckets come to my mind - How about creating 2 columns in Tabe2: Start (Datetime -1 min) and End (Datetime + 1 min)

Then your formula for the calculated columns would look like this:
sorry, some of my Special characters don't work for some reasons:

CALCULATE(VALUES(InboudCalls[Agent_Name]),filter(InboundCalls, InboundCalls[Start]"IsBiggerThan" OutboundState[Datetime]&&InboundCalls[End]"IsSmallerrOrEqualThan"OutboundState[Datetime] && InboundCalls[Agent_Name]=OutboundState[Agent_Name]))

(nasty thief I am...: http://www.mrexcel.com/forum/power-bi/844790-lookup-based-dax-measure.html)

Wonder how this performs with big data volume compared to Scotts first suggestion - maybe you give both a try and let us know?

 

[scottsen]

After my workout I played for a bit, and came up with this:

=CALCULATE(countrows('Call'), FILTER('Call', 'Call'[Agent_Name] = State[Agent_Name] &&
24 * 60 * ABS((State[Start_Date] + State[Start_Time]) - ('Call'[Start_Date] + 'Call'[Start_Time])) <= 1.01))

The 1.01 is cuz... well, floating point math is lame. Probably a better solution there.

Edit for clarity:
This is a calc column on the State table, that should return blank if there were no calls super close to their State starting date/time... and > 0 otherwise. You should be able to them just SUM() this column to break it down by naughty agents.

 

[StandardSanders]

Thanks for all the help. I kept getting an error that StateLog(Start_Time) could not be found or used in the expression so I simply went with the [Time] Column I had previously and removed the 24*60 conversion to be this =CALCULATE(countrows('CDR'), FILTER('CDR', 'ABS((StateLog[Time]) - ('CDR'[Time])) <= 1.01)) and it worked wonderfully.

CDR is my inbound call report and StateLog is my outbound state. The reason I removed the Agent Name lines is because I actually want it to show me not this. Here is why say Bob is about to get a call, he looks up at our queue monitor and see that there is a call in Prequeue (meaning a user is navigating our IVR system) he sets himself to Restroom status and Danny gets the call. What this now allows me to do is see all times that Bob went to restroom when a call was in our Prequeue.

Allows me to automate the process of tracking those agents who are call dodging which anyone who has worked in a call center can understand the pains that causes. y

 

[StandardSanders]

Thanks for all the help. I kept getting an error that StateLog(Start_Time) could not be found or used in the expression so I simply went with the [Time] Column I had previously and removed the 24*60 conversion to be this =CALCULATE(countrows('CDR'), FILTER('CDR', 'ABS((StateLog[Time]) - ('CDR'[Time])) <= 1.01)) and it worked wonderfully.

CDR is my inbound call report and StateLog is my outbound state. The reason I removed the Agent Name lines is because I actually want it to show me not this. Here is why say Bob is about to get a call, he looks up at our queue monitor and see that there is a call in Prequeue (meaning a user is navigating our IVR system) he sets himself to Restroom status and Danny gets the call. What this now allows me to do is see all times that Bob went to restroom when a call was in our Prequeue.

Allows me to automate the process of tracking those agents who are call dodging which anyone who has worked in a call center can understand the pains that causes. y

Nevermind I see why this doesn't work this route you have 10/11 at 1:46 = 1011146 and 10/1 at 11:46 also 1011146 and would give a false pos. I fixed the error i was getting and went with this =CALCULATE(countrows('CDR'), FILTER('CDR', 24 * 60 * ABS((StateLog[Start_Date]) - ('CDR'[Start_Date] <= 1.01)))) and this is working correctly now.

It is a bit spotty because I am stuck on 32 bit and this is a taxing formula.
Thanks again for the help.

 

[StandardSanders]

Nevermind I see why this doesn't work this route you have 10/11 at 1:46 = 1011146 and 10/1 at 11:46 also 1011146 and would give a false pos. I fixed the error i was getting and went with this =CALCULATE(countrows('CDR'), FILTER('CDR', 24 * 60 * ABS((StateLog[Start_Date]) - ('CDR'[Start_Date] <= 1.01)))) and this is working correctly now.

It is a bit spotty because I am stuck on 32 bit and this is a taxing formula.
Thanks again for the help.

Silly question how do I edit on this forum?

Also I spoke too soon this isn't quite doing what I need it to. Removing the agent names did what should've been obvious and just counted all the rows of the CDR table. Derp. Leaving it in does not help me determine when the agent goes on Break or Restroom right as a call is coming in. I'll play with this some more but would love any other ideas but this seems to be in the right direction.

 

[ImkeF]

Did your try the bucket-Approach?

Here is a description of what it does: Bucketing Values in DAX - Analysis Services and PowerPivot Team Blog - Site Home - MSDN Blogs

In your case: Creates a timespan around the time when a call is coming in. (2 new columns if it's actualy +/- 1 - but after reading again what you want, I'd say that one column that adds 1-2 secs will just be enough). Then that calculated column in your outbound table will look for each row into the inbound table if there is a matching bucket "timespan when a call might sit in the queue.

So it should give you a match, if the Agent leaves his desk during the timespan defined in the inbound call table.
But actually, you should use this formula instead, as it will count the number of calls in the inbound table for each instance in the outbound table:

=CALCULATE(COUNT(InboundCall[Agent]);filter(InboundCall; InboundCall[Start]"LessThan"OutboundState[Off]&&InboundCall

#VALUE!