Discussion Board for collaboration on QlikView Layout & Visualizations.
Hi All ,
Requirement is need to get the count of Employee based on the shift start , End TIme and Break Time.
Login/Logoff - > Shift start and end of each employee
Break - > Break should be subtracted based on the start and End Time.
Expected Output :
Looks like you are in need of the IntervalMatch() function. This blog post by HIC explains it well.
You just need to have the interval as Hours.
I think No. Interval Match wont work for it.
I need to find the count of employees who were available during that particular slot
I use the IntervalMatch() function to do just that and it works perfectly.
Can you share the qvw file?
Try this script
Num(Hour(StartTime)) AS StartHour,
Num(Hour(EndTime)) AS EndHour,
Minute(StartTime) AS StartMinute,
Minute(EndTime) AS EndMinute;
[PS ID],Date([Date]) AS Date, [Log Type], TimeStamp(TimeStamp#([Start Time], 'MM/DD/YYYY hh:mm:ss:fff'), 'MM/DD/YYYY hh:mm:ss') AS StartTime,
TimeStamp(TimeStamp#([End Time], 'MM/DD/YYYY hh:mm:ss:fff'), 'MM/DD/YYYY hh:mm:ss') AS EndTime
PS ID, Date, Log Type, Start Time, End Time
123, 6/1/2015, LogIn/LogOff, 04/01/2015 05:00:00:000, 04/01/2015 15:00:00:000
145, 6/1/2015, LogIn/LogOff, 04/01/2015 09:00:00:000, 04/01/2015 18:30:00:000
178, 6/1/2015, LogIn/LogOff, 04/01/2015 13:00:00:000, 04/01/2015 21:00:00:000
123, 6/1/2015, Break, 04/01/2015 08:00:00:000, 04/01/2015 8:30:00:000
145, 6/1/2015, Break, 04/01/2015 11:00:00:000, 04/01/2015 11:15:00:000
178, 6/1/2015, Break, 04/01/2015 17:00:00:000, 04/01/2015 17:30:00:000];
[PS ID], Date,
StartHour + IterNo() - 1 AS Hour,
If([Log Type] = 'Break',
If(EndMinute > 0 AND StartHour + IterNo() - 1 = EndHour, -EndMinute/60, -1),
If(StartMinute > 0 AND IterNo() = 1, StartMinute/60, 1)) AS Value
WHILE StartHour + IterNo() - 1 <= EndHour;
Can you share your file
Please find attached file for solution.