Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@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.
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?