2 Replies Latest reply: Mar 13, 2015 7:19 PM by Thomas Cybucki RSS

    Qlik Sense - Creating Master Time Calendar with 15 minute intervals

    Thomas Cybucki

      Hi Everyone,

       

      I am new to Qliksense and have been checking the community a lot for help on creating some useful visualizations.

       

      My datasource outputs a lot of data, but I am specifically having some trouble with time stamps. I am tracking in and out times for staff.  I have columns called InPunch and Outpunch which have values in the format "m/d/yyyy hh:mm:ss TT." It was easy enough to split them up by doing:

      Year(InPunch) AS InYear,

      Month(InPunch) AS InMonth,

      ...and so on

       

      I currently graph a line in excel that shows how many people I have in the building at all 96 15 minute intervals in a day (ie. 0:00 - 0:15 or 18:30 to 18:45).

       

      I first created a DateBridge to combine my in and out times; applied a CanonicalDate; and Master Calendar found in the community, and it all seems to work when the granularity is 1 day:

       

      EmployeeNumber2InPunch:

      MAPPING LOAD

           EmployeeNumber,

           InPunch

      RESIDENT MYTABLE;

       

      EmployeeNumber2OutPunch:

      MAPPING LOAD

           EmployeeNumber,

           Outpunch

      RESIDENT MYTABLE;

       

      //  **** CREATE A DATEBRIDGE ****

      DateBridge:

      LOAD

             EmployeeNumber,

             APPLYMAP('EmployeeNumber2InPunch',EmployeeNumber,Null()) AS CanonicalDate, 'InPunch' AS DateType

      RESIDENT MYTABLE;

      LOAD

           EmployeeNumber,

           APPLYMAP('EmployeeNumber2OutPunch',EmployeeNumber,Null()) AS CanonicalDate, 'OutPunch' AS DateType

      RESIDENT MYTABLE;

       

      // **** MASTER CALENDAR ****

      Temp:

      LOAD

        min(RangeMin(InPunch,Outpunch)) AS minDate,

        max(RangeMin(InPunch,Outpunch)) AS maxDate

      Resident MYTABLE;

       

      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

       

      DROP Table Temp;

       

      TempCalendar:

      LOAD

      $(varMinDate) + Iterno()-1 As Num,

      Date($(varMinDate) + IterNo() - 1) as TempDate

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

       

      //  **** MASTER CALENDAR ****

      CanonicalCalendar:

      LOAD

           TempDate AS CanonicalDate,

           Week(TempDate) As CanonicalWeek,

           Year(TempDate) As CanonicalYear,

           Month(TempDate) As CanonicalMonth,

           Day(TempDate) As CanonicalDay,

           Timestamp(TempDate,'hh:mm') AS CanonicalTime,

           YeartoDate(TempDate)*-1 as CurYTDFlag,

           YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

           inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

           Floor(date(monthstart(TempDate), 'MMM-YYYY')) as CanonicalMonthYear,

           ApplyMap('QuartersMap', month(TempDate), Null()) as CanonicalQuarter,

           Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as CanonicalWeekYear,

           WeekDay(TempDate) as CanonicalWeekDay

      Resident TempCalendar

      Order By TempDate ASC;

      Drop Table TempCalendar;

       

      I highlighted the Timestamp line in red just because I added that in, but it does not output correctly when I try to filter times. I only see 00:00. That could be because I used the FLOOR function when I loaded my InPunch and Outpunch data though.

       

      How do I adapt this to get the data to snap to a 15 minute interval, and bridge my in and out times for employees?  As I mentioned above, I want to be able to graph a line eventually showing an employee headcount by 15 minute interval, so it needs to take into consideration employees who come and go at all times of the day. Any help would be greatly appreciated!

       

      Thank you!