Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Determine work hours on mo-fri, sat, sun based on start and end time

Hi everyone,

For the start and end dates of workingshifts I want to determine how much time of the shift was worked during normal workdays (mon-fri), on saturdays and sundays. I don't really know which commands can be used for these types of calculations. Any suggestions?

11 Replies
Sokkorn
Honored Contributor

Re: Determine work hours on mo-fri, sat, sun based on start and end time

Hi,

Can you attached a small file with your requirement? It can easy to understand well with your requirement.

Or first you can try this solution : EndTime - StartTime

Regards,

Sokkorn

Determine work hours on mo-fri, sat, sun based on start and end time

Hi,

Use Network days command

networkdays ( )  

Let me know about this.

Rgds

Anand

Determine work hours on mo-fri, sat, sun based on start and end time

Hi,

Use some thing like this

NetWorkDays('2009-08-17','2009-08-24', Holidays list ))

Rgds

Anand

Not applicable

Determine work hours on mo-fri, sat, sun based on start and end time

Thanks for the replies. I think my question is not completely clear. Say I have someone who starts working friday at 9:15 pm till saturday 4:30 am. I would like to have the following result:

MonFri = 2:45

Sat = 4:30

SunHours = 0

Thanks for any help!

chauhans85
Esteemed Contributor

Determine work hours on mo-fri, sat, sun based on start and end time

good try anand but

this one is the exact syntax of networkdays

networkdays(Startdate,enddate,holidaydate1,holidaydate2,holidaydate3,.....)

thanks

Sunil

Re: Determine work hours on mo-fri, sat, sun based on start and end time

Hi

Use code some thing like below

=Networkdays ( '2011-09-11' ,  '2011-09-17' , "Sat" , "Sun" )

It gives 5

or

if you had column

=Networkdays ( StartDate ,  EndDate , "Sat" , "Sun" )

Rgds

Anand

Re: Determine work hours on mo-fri, sat, sun based on start and end time

Hi

Use code some thing like below

=Networkdays ( '2011-09-11' ,  '2011-09-17' , "Sat" , "Sun" )

It gives 5

or

calculate it with straight table

See the attached sample file for this

Rgds

Anand

Re: Determine work hours on mo-fri, sat, sun based on start and end time

Hi,

See the updated file

Let me know about this

HTH

Rgds

Anand

Not applicable

Re: Determine work hours on mo-fri, sat, sun based on start and end time

Hi Anand,

This is not exactly what I'm looking for. If I have the Inline load below I would require the result shown

SHIFTS:
LOAD * INLINE [
    Shift, Start, End
    A, 23:45 23-09-2011, 04:00 24-09-2011
    B, 04:00 24-09-2011, 06:00 24-09-2011
    C, 17:00 23-09-2011, 19:00 23-09-2011
    D, 11:00 24-09-2011, 16:00 24-09-2011
    E, 23:12 24-09-2011, 05:00 25-09-2011
    F, 07:00 25-09-2011, 12:00 25-09-2011
];

RESULTS:

         Mon-Fri          Sat          Sun

A          0:15           4:00           0

B            0              2:00           0

C          2:00              0             0

D            0              5:00           0

E            0              0:48         5:00    

F            0                 0           5:00

Community Browser