topic Calculating Hours Worked in QlikView

Calculating Hours Worked

tmumaw — Thu, 14 Apr 2011 15:31:41 GMT

Hi Everyone,

I have a small problem. I am trying to calculate hours worked. I have a table which stores how much time was spent working on a job. I need to add up the time and show how many hours were spent working on the job. When I add-up a group of times the answer is 7.75 and should be 8.3. It's stored in the database as 5 position 2 decimals. Not as timestamp. Thanks in advance.

Thom

Calculating Hours Worked

erichshiino — Thu, 14 Apr 2011 15:58:16 GMT

Can you send us a data sample and the expressions you are using?

Rgds,

Erich

Calculating Hours Worked

tmumaw — Thu, 14 Apr 2011 16:12:19 GMT

Contract

Contrac Line

Status

Date

Integer

Expression Default

Fixed to 1

7.75

7.8

43188674

3-Jan-11

0.25

0.3

43188674

6-Jan-11

0.25

0.3

43188674

10-Jan-11

0.25

0.3

43188674

13-Jan-11

0.25

0.3

43188674

17-Jan-11

0.25

0.3

43188674

20-Jan-11

0.25

0.3

43188674

24-Jan-11

0.25

0.3

43188674

27-Jan-11

0.25

0.3

43188674

31-Jan-11

0.25

0.3

43188674

3-Feb-11

0.5

43188674

7-Feb-11

0.25

0.3

43188674

10-Feb-11

0.5

43188674

14-Feb-11

0.25

0.3

43188674

17-Feb-11

0.5

43188674

24-Feb-11

0.5

43188674

3-Mar-11

0.5

43188674

10-Mar-11

0.5

43188674

17-Mar-11

0.5

43188674

24-Mar-11

0.5

43188674

31-Mar-11

0.5

43188674

7-Apr-11

0.5

Sum(Hours) with different numeric layouts

Calculating Hours Worked

Anonymous — Thu, 14 Apr 2011 17:37:38 GMT

Hi Thom,

If I got it correctly - 7.75 in your data means 7h 45mins. Is that correct?

I think breaking the numbers in two parts (xx.yy) and using the formula sum(xx + yy*3/5) should translate this in proper hours.

Try this one:

sum ( floor (Expression Default) + frac(Expression Default)*3/5 )

and let me know if it works, please;

---------------------------------------------------------------------------------------------------------

Dilyana Ivanova
Consultant, CapricornVentis
QlikView Elite Partner

Calculating Hours Worked

tmumaw — Thu, 14 Apr 2011 18:26:52 GMT

Dilyana,

My 7.75 should be 8 hrs. and 15 mins. or 8.25.

Thanks

Thom

Calculating Hours Worked

erichshiino — Sat, 23 Apr 2011 04:31:58 GMT

I'm sorry, I meant the raw data as well. I'd like to see this part:

' It's stored in the database as 5 position 2 decimals.'

Calculating Hours Worked

Sat, 23 Apr 2011 07:00:00 GMT

hi boss,

cau tou try this once

interval(time#([Hours Worked],'hh:mm:ss'),'hh:mm') as Worked_Hours

thanks,

Calculating Hours Worked

Sat, 23 Apr 2011 07:00:10 GMT

hi boss,

cau tou try this once

interval(time#([Hours Worked],'hh:mm:ss'),'hh:mm') as Worked_Hours

thanks,