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

    15-Minute Intervals: Distribution of Minutes

    Jason Campbell

      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:

      adh1.PNG

      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!

        • Re: 15-Minute Intervals: Distribution of Minutes
          Stefan Wühl

          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 ...;

            • Re: 15-Minute Intervals: Distribution of Minutes
              Jason Campbell

              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
                  Stefan Wühl

                  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
                      Jason Campbell

                      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:

                       

                      StartStop#Mins
                      8:008:1515
                      8:158:3015
                      8:308:4515
                      8:459:005

                       

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

                       

                      StartStop#Mins
                      8:008:1512
                      8:158:3015
                      8:308:4515
                      8:459:008

                       

                      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.

                        • Re: 15-Minute Intervals: Distribution of Minutes
                          Stefan Wühl

                          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