Problem in Getting Actual Duration in Mins

[umerikhlas]

Dear Fellow Members,

Attached please find the excel sheet containing Shift Details and Employee Time In and Out Details.

Using interval match I was able to find the Duration In Mins an Employee worked for but it includes Break Time (In Mins)

Note: Shifts and break times are not fixed.

Any help would be appreciated.

Regards,

UI

[Anil_Babu_Samineni]

What are the issue?

[Gysbert_Wassenaar]

See this discussion: Task/Ticket Duration

[effinty2112]

Hi Umer,

                    Try:

Interval(End -Start-BreakInMins/1440)

Kind regards

Andrew

[ajsjoshua]

Hi,

check below

Breakdown time

[umerikhlas]

The above mentioned solutions are not working in my case. Any further help would be appreciated as it is a mission critical requirement.

UI

[adamdavi3s]

Why are they not working?

How are you matching shifts to breaks?

Can you provide a sample qvd with your working in so we at least have something to go on?

[umerikhlas]

Hi All Qlikers,

Did some work with the help of my colleagues.

Shift Details (with Start and End Times and Break In Minutes) shown in figure 1.

Employee Details (with Time In and Time Out) shown in figure 2.

Sum of Breaks can be found in figure 3 in field named as Test.

Now, Problem is that it works correctly only if the Shift Start Time is exact equal to (with seconds) as Employee Time In and Shift End Time is exact equal to (with seconds) as Employee Time Out, which would not be the case every time.

Any help would be appreciated.

Sample files attached.

Kind Regards,

UI

[Anonymous]

Hi Umer,

To load your data (script) :

Timing:
LOAD
    EmpID,
    EmpName,
    Timestamp(TimeStamp#(TimeIn,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') AS TimeIn,
    TimeStamp(TimeStamp#(TimeOut,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') AS TimeOut;
LOAD * INLINE [
    EmpID, EmpName, TimeIn, TimeOut
    1001, Yawar, 8/27/16 7:00, 8/27/16 14:15
    1002, Jahanzaib, 8/27/16 9:10, 8/27/16 20:00
    1003, Sehan, 8/27/16 7:00, 8/27/16 23:59
];

Times:
LOAD
    Shift,
    Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') AS Date,
    Timestamp(TimeStamp#(Start,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') AS Start,
    TimeStamp(TimeStamp#(End,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') AS End,
    BreakInMins;
LOAD * INLINE [
    Shift, Date, Start, End, BreakInMins
    1, 27/08/2016, 8/27/16 7:00, 8/27/16 9:00, 0
    2, 27/08/2016, 8/27/16 9:10, 8/27/16 12:00, 10
    3, 27/08/2016, 8/27/16 12:30, 8/27/16 14:15, 30
    4, 27/08/2016, 8/27/16 14:25, 8/27/16 18:00, 10
    5, 27/08/2016, 8/27/16 18:10, 8/27/16 20:00, 10
    6, 27/08/2016, 8/27/16 20:30, 8/27/16 22:00, 30
    7, 27/08/2016, 8/27/16 22:10, 8/27/16 23:59, 10
    1, 28/08/2016, 8/28/16 7:00, 8/28/16 9:00, 0
    2, 28/08/2016, 8/28/16 9:20, 8/28/16 12:10, 20
    3, 28/08/2016, 8/28/16 12:45, 8/28/16 14:30, 35
    4, 28/08/2016, 8/28/16 14:40, 8/28/16 18:20, 10
    5, 28/08/2016, 8/28/16 18:25, 8/28/16 20:00, 5
    6, 28/08/2016, 8/28/16 20:30, 8/28/16 21:55, 30
    7, 28/08/2016, 8/28/16 22:15, 8/28/16 23:59, 20
];

IntervalMatch (Date) LOAD TimeIn, TimeOut Resident Timing;

LEFT JOIN(Timing)
LOAD * RESIDENT Times;

DROP TABLE Times;

NoConcatenate
FinalTiming:
LOAD
    Shift, 
    Date, 
    Start, 
    End, 
    BreakInMins,
    EmpID, 
    EmpName, 
    TimeIn, 
    TimeOut
RESIDENT Timing
WHERE Start >= TimeIn and End <= TimeOut;

DROP Table Timing;

To calculate Interval (front-end):

Interval(Sum(Aggr(TimeOut-TimeIn-Interval(Interval#(Sum(BreakInMins),'mm')),EmpID,TimeIn,TimeOut)),'mm')

Regards!!