6 Replies Latest reply: May 10, 2017 11:34 AM by Jason Campbell

# 15-Minute Intervals: Distribution of Minutes

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

Date(TimeStamp) as intvlDate
,
Time as intvlTime;

Timestamp(\$(vAdhMinDate) + (RecNo() - 1)/96) as TimeStamp
,
Time((RecNo() - 1)/96,'h:mm') as Time

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!

• ###### Re: 15-Minute Intervals: Distribution of Minutes

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

TimestampField as Timestamp,

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

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

Dayname(TimestampField) as Date

FROM ...;

• ###### Re: 15-Minute Intervals: Distribution of Minutes

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?

• ###### Re: 15-Minute Intervals: Distribution of Minutes

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?

• ###### Re: 15-Minute Intervals: Distribution of Minutes

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:

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;

,'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

,excpt.name_ AS schShiftName

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

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

• ###### Re: 15-Minute Intervals: Distribution of Minutes

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
14808:00:00 AM15
14958:15:00 AM15
15108:30:00 AM15
15258:45:00 AM5
24808:00:00 AM12
24958:15:00 AM15
25108:30:00 AM15
25258:45:00 AM8
• ###### Re: 15-Minute Intervals: Distribution of Minutes

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