Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Calculating Employees Available at Interval

Hi All ,

Requirement is need to get the count of Employee based on the shift start , End TIme and Break Time.

Input:

  

PS IDDate[Log Type][Start Time][End Time]
1236/1/2015LogIn/LogOff04/01/2015 05:00:00:00004/01/2015 15:00:00:000
1456/1/2015LogIn/LogOff04/01/2015 09:00:00:00004/01/2015 18:30:00:000
1786/1/2015LogIn/LogOff04/01/2015 13:00:00:00004/01/2015 21:00:00:000
1236/1/2015Break04/01/2015 08:00:00:00004/01/2015 8:30:00:000
1456/1/2015Break04/01/2015 11:00:00:00004/01/2015 11:15:00:000
1786/1/2015Break04/01/2015 17:00:00:00004/01/2015 17:30:00:000

Login/Logoff - > Shift start and end of each employee

Break  - > Break should be subtracted based on the start and End Time.

Expected Output :

 

IntervalCount
0 -10
1 -20
2-30
3-40
4-50
5-61
6-71
7-81
8-90.5
9-102
10-112
11-121.75
12-132
13-143
14-153
15-162
16-172
17-181.5
18-191.5
19-201
20-211
21-220
22-230
23-240
7 Replies

Re: Calculating Employees Available at Interval

Looks like you are in need of the IntervalMatch() function.  This blog post by HIC explains it well.

     IntervalMatch

You just need to have the interval as Hours.

Not applicable

Re: Calculating Employees Available at Interval

I think No. Interval Match wont work for it.

I need to find the count of employees who were available during that particular slot

Re: Calculating Employees Available at Interval

I use the IntervalMatch() function to do just that and it works perfectly.

Not applicable

Re: Calculating Employees Available at Interval

Can you share the qvw file?

MVP
MVP

Re: Calculating Employees Available at Interval

Hi,

Try this script

Temp:

LOAD

*,

Num(Hour(StartTime)) AS StartHour,

Num(Hour(EndTime)) AS EndHour,

Minute(StartTime) AS StartMinute,

Minute(EndTime) AS EndMinute;

LOAD

[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

INLINE [

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

LOAD

[PS ID], Date,

[Log Type],

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

RESIDENT Temp

WHILE StartHour + IterNo() - 1 <= EndHour;

Regards,

Jagan.

Not applicable

Re: Calculating Employees Available at Interval

HI Jagan

Can you share your file

MVP
MVP

Re: Calculating Employees Available at Interval

Hi,

Please find attached file for solution.

Regards,

Jagan.

Community Browser