Qlik Sense - Creating Master Time Calendar with 15 minute intervals

tcybucki — Wed, 11 Mar 2015 22:03:34 GMT

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!

Re: Qlik Sense - Creating Master Time Calendar with 15 minute intervals

Gysbert_Wassenaar — Thu, 12 Mar 2015 17:44:37 GMT

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

Re: Qlik Sense - Creating Master Time Calendar with 15 minute intervals

tcybucki — Fri, 13 Mar 2015 23:19:05 GMT

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!

topic Re: Qlik Sense - Creating Master Time Calendar with 15 minute intervals in App Development

Qlik Sense - Creating Master Time Calendar with 15 minute intervals

Re: Qlik Sense - Creating Master Time Calendar with 15 minute intervals

Re: Qlik Sense - Creating Master Time Calendar with 15 minute intervals