Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to sum and calculate time?

Skärmavbild 2014-11-03 kl. 15.41.36.png

Hi all, I have this data, and would like to sum the time worked. P_TYPE = 1 is for when You sign in, and P_TYPE = 2 is for signing out for breaks or end of day.  so my question is hos to do this?

Thanks in advance!

11 Replies
Gysbert_Wassenaar

As long as you have the same number of check ins and check outs per day you can use rangesum(sum({<P_TYPE={2}>}P_TIME),-sum({<P_TYPE={1}>}P_TIME))


talk is cheap, supply exceeds demand
MK_QSL
MVP
MVP

Use Below Script.

Temp:

Load

  Time#(P_TIME,'hh:mm') as P_TIME,

  P_TYPE,

  If(P_TYPE = 1 , 'Sign In', 'Sign Out') as Flag,

  Date(Date#(%Key_date,'YYYY-MM-DD')) as %Key_date

Inline

[

  P_TIME, P_TYPE, %Key_date

  05:52, 1, 2014-10-29

  09:00, 2, 2014-10-29

  09:32, 1, 2014-10-29

  12:02, 2, 2014-10-29

  12:29, 1, 2014-10-29

  15:00, 2, 2014-10-29

];

Final:

Load

  P_TIME,

  If(P_TYPE = 1 and Previous(P_TYPE) = 2, Interval(P_TIME - Previous(P_TIME),'hh:mm')) as Diff,

  P_TYPE,

  Flag,

  %Key_date

Resident Temp

Order By P_TIME;

Drop table Temp;

Use below expression to get the Net Working Hours

=Interval(Interval(Max(P_TIME)-Min(P_TIME),'hh:mm') - SUM(Diff),'hh:mm')

simondachstr
Luminary Alumni
Luminary Alumni

Without a unique identifier e.g. worksessionid, how are you identifying the right P_TYPE=2 to be subtracted by the respective P_TYPE=1 values?

e.g. 15:00 (which is the last row in the table).. will you be subtracting 5:52, 9:32 or 12:29?

Gysbert_Wassenaar

Example data:

check-in, check-out

1,     2,

3,     4

2-1=1

4-3=1

total = 1+1 = 2

or

2-3=-1

4-1=3

total = -2+3 =2

or sum of check-outs minus sum of check-ins:

4+2=6

3+1=4

total = 6-4 = 2

Conclusion: it does not matter as long as the number of check-ins is the same as the number of check-outs.


talk is cheap, supply exceeds demand
simondachstr
Luminary Alumni
Luminary Alumni

I agree the totals will be the same, but if you want to sum (or calculate) the hours worked between check-in and check-out for each working session (e.g. You want to flag the people who work more than 4 hours) then your example will not work. But this, of course, depends on the requirement.

Not applicable
Author

Skärmavbild 2014-11-03 kl. 16.45.59.png

I have this field NR also. so you could basicly say, that nr 2-1 and nr 4-3 and 6-5 and so on. if that helps?

its_anandrjs

Hi Rickard,

If you want to calculate the total duration was attempted by the employee so with the help of Interval we can calculated the In and Out time difference and then sum total hour attempted. Have a look the script of that

Source:

LOAD Time#(P_TIME,'hh:mm') as P_TIME,P_TYPE,%Key_date,RowNo() as Rowid;

LOAD * Inline

[

P_TIME,P_TYPE,%Key_date

05:52,1,2014-10-29

09:00,2,2014-10-29

09:32,1,2014-10-29

12:02,2,2014-10-29

12:29,1,2014-10-29

15:00,2,2014-10-29

];

Final:

LOAD

%Key_date,Rowid, P_TIME as [P Time],

if(P_TYPE=1,P_TIME) AS InTime,

if(P_TYPE=2,P_TIME) AS OutTime,

If(Previous(P_TYPE) = 1 and P_TYPE = 2, Interval(P_TIME - Previous(P_TIME),'hh:mm'))  AS DIFF

Resident Source;

DROP Table Source;

And then in the front end in straight table take

Dimension:-  %Key_date


Expression:- Sum(DIFF)


And you get chart as

TotHours.png

The total hours for date 214-10-29 is 08:09 is it correct


Regards

Anand

Not applicable
Author

Skärmavbild 2014-11-04 kl. 15.09.03.pngSkärmavbild 2014-11-04 kl. 15.06.54.png

as you can see my DIFF isnt correct, but everything else seems to be, what do I need to change?

MK_QSL
MVP
MVP

have you tried my solution?