topic Re: How to calculate working time in QlikView

How to calculate working time

Mon, 20 Oct 2014 17:58:10 GMT

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 :

Projet	TimeStamp Start	TimeStamp End
Projetct A	2014-02-03T09:00:00	2014-02-04T17:20:00
Project B	2014-02-05T15:37:00	2014-02-10T09:50:05
...

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

Day	MorningStart	MorningEnd	AfterNoonStart	AfterNoonEnd
Monday	08:00:00	12:00:00	14:00:00	18:00:00
... (the same as monday)
Friday	08:00:00	12:00:00	14:00:00	17:00:00

And a last table with each day off :

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

The result should be :

Project	ProjectWorkingTime (minutes)
Project A	1040 (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 B	673

Re: How to calculate working time

maxgro — Mon, 20 Oct 2014 18:40:42 GMT

there are some good post of jagan on working days calculation

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

Re: How to calculate working time

Tue, 21 Oct 2014 09:39:50 GMT

Thanks Massimo,

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

The main problem is the working hours.

Re: How to calculate working time

tresB — Tue, 21 Oct 2014 09:46:01 GMT

Have a look here: Calculate hours between two Date/Time strings | Qlik Community

Re: How to calculate working time

Tue, 28 Oct 2014 17:30:07 GMT

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