Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tcybucki
Contributor II
Contributor II

Qlik Sense - Creating Master Time Calendar with 15 minute intervals

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!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

I wouldn't create the quarters in the master calendar table. I think it's better to create a Master Time table. See this blog post for the reasons: The Master Time Table

MasterTime:

LOAD time(recno()/96,'hh:mm') as Time

autogenerate(96);


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

I wouldn't create the quarters in the master calendar table. I think it's better to create a Master Time table. See this blog post for the reasons: The Master Time Table

MasterTime:

LOAD time(recno()/96,'hh:mm') as Time

autogenerate(96);


talk is cheap, supply exceeds demand
tcybucki
Contributor II
Contributor II
Author

So I commented out my entire Master Calendar and added the MasterTime table as suggested.  I kept the DateBridge so I can connect my in and out dates.

Thank you Gysbert, it seems to work now!