Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to calculate working time

Hi all,

I am trying to calculate the working time (in minutes) between two timestamps including working time and working days (...).

I have made a lot of tries and I found a working solution.

However, my solution is not relevant because it uses too many "if" statement combined with the intervalmatch function.

The script is too complicated !

Did someone meet the same problem ? How did you solve it ? Did you find a smarter solution ?

Thanks for your help and your time !

Main data are looking like this :

ProjetTimeStamp StartTimeStamp End
Projetct A2014-02-03T09:00:002014-02-04T17:20:00
Project B2014-02-05T15:37:002014-02-10T09:50:05
...

I have another Table with the working hours / day in the week :

DayMorningStartMorningEndAfterNoonStartAfterNoonEnd
Monday08:00:0012:00:0014:00:0018:00:00
... (the same as monday)
Friday08:00:0012:00:0014:00:0017:00:00

And a last table with each day off :

Day Off
2014-02-06
2014-08-08
...

The result should be :

ProjectProjectWorkingTime (minutes)
Project A1040 (calc : 09:00:00->12:00:00 + 14:00:00->18:00:00 + 08:00:00->12:00:00 + 14:00:00->17:20:00)
Project B673
Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: How to calculate working time

4 Replies
MVP
MVP

Re: How to calculate working time

there are some good post of  jagan on working days calculation

http://community.qlik.com/search.jspa?q=working+day&author=%2Fpeople%2F23109

Not applicable

Re: How to calculate working time

Thanks Massimo,

Unfortunately, the problem is not on working days wich I can handle with NetWorkDays(...) function.

The main problem is the working hours.

MVP
MVP

Re: How to calculate working time

Not applicable

Re: How to calculate working time

Hello,

Sorry for this late reply.

Thanks Tresesco, this post was very helpful.

I have attached an example of my solution in a sub function.

An excel file is attached and used to list Holydays and Working hours.

SUB CALC_DURATION(TableName,KeyField,StartDate,EndDate,DurationFieldName,CalendarParamDir)

  //Generate each day between StartDate and EndDate

  tmpCALC_DURATION:

  LOAD $(KeyField),

  iterno() as Iter,

  daystart($(StartDate))+iterno()-1 as Date, 

  weekday(daystart($(StartDate))+iterno()-1) as DayNumber,

  $(StartDate),

  $(EndDate)

  RESIDENT $(TableName)

  WHILE DAYSTART($(EndDate)) >= DAYSTART($(StartDate))+ITERNO()-1;

  //Add Holidays Table

  LEFT JOIN(tmpCALC_DURATION)

  LOAD

  Date, //key

      

      IsHoliday

  FROM

  $(CalendarParamDir)CalendarParams.xlsx

  (ooxml, embedded labels, table is Holiday_Table);

  //Add Working Hours table

  LEFT JOIN(tmpCALC_DURATION)

  LOAD 

  DayNumber, //key

     

      time(DayStart) as DayStart,

      time(BreakStart) as BreakStart,    

      time(BreakEnd) as BreakEnd,

      time(DayEnd) as DayEnd

     

  FROM

  $(CalendarParamDir)CalendarParams.xlsx

  (ooxml, embedded labels, table is WorkingHours_Table);

  //Calc Start hour and en hour for each day in the week

  tmpCALC_DURATION_2:

  LOAD

  $(KeyField), 

  Date,

  DayStart,

  BreakStart,

  BreakEnd,

  DayEnd,

  IsHoliday,

  

  if(Iter=1,

  rangemin(rangemax(frac($(StartDate)),DayStart),DayEnd)

  ,DayStart) as Start,

  if(Date=daystart($(EndDate)),

  rangemax(DayStart,rangemin(frac($(EndDate)),DayEnd))

  ,DayEnd) as End

  RESIDENT tmpCALC_DURATION;

  DROP TABLE tmpCALC_DURATION;

  //Calc Duration with End and Start

  LEFT JOIN($(TableName))

  LOAD

  $(KeyField), //key

  interval(

  sum(End-Start+

  if(Start<BreakStart and End>BreakStart,

  BreakStart-BreakEnd

  ,0)

  )

  ) as $(DurationFieldName)

  RESIDENT tmpCALC_DURATION_2

  WHERE IsHoliday<>1

  GROUP BY $(KeyField);

  DROP TABLE tmpCALC_DURATION_2;

END SUB