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!
