Looking at the frequency and time difference of employees taking breaks

[Ryuuk]

Hi all,

I am new to QlikSense and I am making a dashboard to see how many times employees go outside the working place to take a break or go to the toilet. I need to make a top 10 outlier table with the top 10 persons that go the most out of the working place and take the longest time. 

I am very new to QlikSense and hope someone can help me with the two formulas, I already created a table with the IN/OUT count in a colom. The top 10 table should contain the ID, frequency and time taken. It is on multiple dates but I already put a date filter in the dashboard. I put some psuedo data as a picture to show how my table looks like, in the IN and OUT columns the 1 represents TRUE. I hope you guys can help me out!

[sidhiq91]

@Ryuuk  Please see the code below that I have first used in the back end:

NoConcatenate
Temp:
Load ID,Name,Date(Date#(Date,'DD-MMM'),'DD-MMM') as Date,
Timestamp(Time,'hh:mm') as Time,
IN,
OUT

Inline [
ID, Name, Date, Time, IN, OUT
663320,John, 25-Jul,18:45,1,
663320,John, 25-Jul,18:47,,1
663320,John, 25-Jul,19:30,1,
663320,John, 25-Jul,20:30,,1
];

NoConcatenate
Temp1:
Load *,
if(ID=Previous(ID) and Date=Previous(Date) and OUT='1', Interval(Time-Previous(Time),'hh:mm')) as Duration

Resident Temp
order by ID, Date;

Drop table Temp;

exit script;

Then in the front end, use the sum(Duration).

I hope it resolves your issue.

[Ryuuk]

Hi,

This worked eventually in the back-end. I have a follow-up question and hope you could help me with this. 

The employees work also night shifts and thus if we look at a working day, we have to look at two days but I don't know how to incorperate this into the script that two days could be seen as one for only the night shifts. 

Do you know a solution to this?