Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
umerikhlas
Contributor III
Contributor III

Problem in Getting Actual Duration in Mins

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

8 Replies
Anil_Babu_Samineni

What are the issue?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Gysbert_Wassenaar

See this discussion: Task/Ticket Duration


talk is cheap, supply exceeds demand
effinty2112
Master
Master

Hi Umer,

                    Try:

Interval(End -Start-BreakInMins/1440)

Kind regards

Andrew

ajsjoshua
Specialist
Specialist

Hi,

check below

Breakdown time

umerikhlas
Contributor III
Contributor III
Author

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
Master
Master

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
Contributor III
Contributor III
Author

Hi All Qlikers,

Did some work with the help of my colleagues.

image.png

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

manucamon
Specialist III
Specialist III

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!!