Thinking CAP ON! Need to count employees by time based on in/out time stamps

[billuran]

Hello All, This is one where i think we can all work together on: I have created a solution in excel, but i want to take it into qlik.

I want to count the number of employees working every half hour (or a designated time increment) by when they clocked in and out.

Example:

Date Set

Employee ID	Time Stamp In	Time Stamp Out
1	1/7/17 8:00	1/7/17 23:00
2	1/7/8:00	1/7/17 15:00
3	1/7/10:00	1/7/1714:00

Things to consider: Want to be able to filter by day of week and if the time goes over midnight to the next day need to assign remaining hours to the next day or days.

Lets do this!!

Bill

[Gysbert_Wassenaar]

Please don't clutter this site with duplicate discussions: Get your thinking caps on! Want to chart clockin/outs and time between‌

What you can try is similar to described in this blog post: Creating Reference Dates for Intervals‌. Instead of generating days you would generate half hour segments. Something like this:

LOAD

     [Employee ID],

     [Time Stamp In],

     [Time Stamp Out]

      Timestamp( [Time Stamp In] + (IterNo() – 1)/48 ) as ReferenceHalfHourTimestamp

FROM

     ....

WHILE

     [Time Stamp In] + (IterNo() – 1)/48 <= [Time Stamp Out]

     ;

[billuran]

Agreed but I don't want to limit followers of just sense or view, since its a universal question.