Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
there are some good post of jagan on working days calculation
http://community.qlik.com/search.jspa?q=working+day&author=%2Fpeople%2F23109
Thanks Massimo,
Unfortunately, the problem is not on working days wich I can handle with NetWorkDays(...) function.
The main problem is the working hours.
Have a look here: Calculate hours between two Date/Time strings | Qlik Community
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