15-Minute Intervals: Distribution of Minutes

[jcampbell474]

I am tasked with creating call center schedule adherence metrics in Qlikview and have hit a wall.  Hoping someone here can point me in the right direction.

I have all of the scheduled and actual ‘exceptions’ or codes and the start and stop times for each associate.  The times are in the format of mins (number of mins after midnight).  I converted them to hh:mm (minutes/1440).

Here is a small sample of the data:

Everything is coded and data is in the app, but I now need to put the number of minutes each exception has into 15-minute intervals throughout the day.  In other words, distribute the minutes into applicable 15-minute intervals. I created an interval table:

LET vAdhMinDate = Num(MakeDate(2017,4,1));  // Calendar Start Date
LET vAdhMaxDate = Num(Today()); // Calendar End Date
LET vAdhDays = vAdhMaxDate - vAdhMinDate + 2;  // Calculating number of days between Start & End Dates

adhIntervals:
LOAD
Date(TimeStamp) as intvlDate
,Time as intvlTime;

LOAD
Timestamp($(vAdhMinDate) + (RecNo() - 1)/96) as TimeStamp
,Time((RecNo() - 1)/96,'h:mm') as Time
AUTOGENERATE 96 * $(vAdhDays);

Now, should I create a Timestamp field in the Interval table, then load data with the timestamp field converted to interval and join using it? Sum the number of minutes in each interval?  Using the start time, how can I populate each 15-minute interval with the duration, decremented 15-minutes each time? I can’t envision how this should look.   

Any help would be appreciated.

Thanks!

[swuehl]

Maybe just Round() / Ceil() / Floor() your numbers to create 15 min intervals:

LOAD

     TimestampField as Timestamp,

     Timestamp(Round( TimestampField, 1/96)) as TimestampInterval,

     Time(Frac(Round( TimestampField, 1/96))) as TimeInterval,

     Dayname(TimestampField) as Date

FROM ...;

[jcampbell474]

Thank you, Stefan.  That would certainly create 15-min intervals, but will it decrement the schShiftLength (mins) by 15 in each interval?  Or will it populate the total mins (510) for each interval?

[swuehl]

Not sure I understand what you are trying to achieve.

Something like creating reference timestamps?

Creating Reference Dates for Intervals

(with timestamps instead of integer dates, but same principle applied)?

Could you post some sample input records and what you expect to see?

[jcampbell474]

I do not need a Reference Timestamp.  Some might refer to it as a matrix, commonly used in call centers for interval reporting.

Assuming an associate has 50-minutes of time logged in, I need to distribute it across 15-minute intervals.  The fields I'm working with only give the Shift Start Time and Duration(mins).  So, if the associate logged in at 8:00, I would need the 50-minute distribution to look like this:

Start	Stop	#Mins
8:00	8:15	15
8:15	8:30	15
8:30	8:45	15
8:45	9:00	5

If they logged in at 8:03, I would need it to look like this:

Start	Stop	#Mins
8:00	8:15	12
8:15	8:30	15
8:30	8:45	15
8:45	9:00	8

I need to apply this to many different fields, (Scheduled Shift, lunch, breaks, etc... vs Actual Shift, lunch, breaks, etc...) and compare them to deduce Scheduled vs. Actual % Adherence.

Here is some of the code I'm using:

SchShift:

Load

  Date(Floor(schedate),'YYYY-MM-DD')&'|'&'I'&agent_ as %DateEmpKey

  ,agent_&'|'&schedID_ as schShiftKey

  ,schedID_ as schedID_key

  ,if(schShiftExc = 46,'Yes','No') as Shift

  ,schShiftExc

  ,schShiftName

  ,schShiftStart

  ,schShiftlength_

  ,schShiftDuration

  ,schShiftEnd;

SELECT schedheader.agent_ AS agent_

  ,schedheader.schedID_

  ,schedheader.date_ as date_

  ,'20' + SUBSTRING(convert(VARCHAR(7), schedheader.[date_]), 2, 2) + '-' + SUBSTRING(convert(VARCHAR(7), schedheader.[date_]), 4, 2) + '-' + SUBSTRING(convert(VARCHAR(7), schedheader.[date_]), 6, 2) schedate

  ,schedheader.excode_ AS schShiftExc

  ,excpt.name_ AS schShiftName

  ,Convert(VARCHAR(5), DateAdd(Minute, schedheader.startMinute_, 0), 108) AS schShiftStart

  ,cast(schedheader.length_ as int) AS schShiftlength_

  ,Convert(VARCHAR(5), DateAdd(Minute, schedheader.length_, 0), 108) AS schShiftDuration

  ,Convert(VARCHAR(5), DateAdd(Minute, schedheader.startMinute_ + schedheader.length_, 0), 108) AS schShiftEnd

FROM [DB] schedheader

LEFT JOIN [DB] excpt on excpt.code_ = schedheader.excode_

Where schedheader.date_ >= 1170401;

Does this help?  Perhaps there is opportunity for me to explain it better.

[swuehl · Accepted solution]

Yes, I think I understood now a little better your requirement.

So your first task is to distribute an amount across several buckets. You can find some threads here in the forum that show how you can do this, AFAIR it's most often associated with budget distribution.

There are different approaches, here is one possible:

// Note that I create a 'minutes from daystart' based on the time, I think you already start with this type of data

INPUT:

LOAD *, RecNo() as RecID, Num#(Interval(Start,'mm')) as StartMinute INLINE [

Start, Duration

08:00, 50

08:03, 50

];

// Create a record per minute of the task (RecID) using a WHILE loop, then create 15 min buckets using Floor and

// aggregate the expanded records down to the bucket granularity

Intervals:

LOAD RecID, IntervalMinute, Time(IntervalMinute/ 1440) as Interval, Count(RecID) as DurationInterval

GROUP BY RecID, IntervalMinute;

LOAD RecID, Floor(StartMinute+Iterno()-1, 15) as IntervalMinute

RESIDENT INPUT

WHILE IterNo() <= Duration;

exit script;

RecID	IntervalMinute	Interval	DurationInterval
1	480	8:00:00 AM	15
1	495	8:15:00 AM	15
1	510	8:30:00 AM	15
1	525	8:45:00 AM	5
2	480	8:00:00 AM	12
2	495	8:15:00 AM	15
2	510	8:30:00 AM	15
2	525	8:45:00 AM	8

[jcampbell474]

Thank you very much, sir.  That's exactly what I need!