Timestamp Difference in Working Hours

[#1]

Hi,

I am wondering what's the best expression to calculate timestamps difference in working hours based on working days Monday to Friday and working hours 9am to 5pm.

e.g. Application was entered on 27/09/2013  14:47:47 and decisioned on 30/09/2013  14:30:10. Based on the above logic c 2.15 hours on 27/09/2013, 28th & 29th are Saturday & Sunday and 5.5 hours on 30/09/2013. So the result should be c 7.7 hours. any idea

[#2]

I understand, that you have some rows of data (for example tasks) with start and end time and some task_id.

You may prepare calendar of working days (should have 2 timestamps for each working day and workday_id field).

Then you should intevalmatch start_working_day field with tasks and end_workng_day with tasks.

Then join results of those 2 results of intervalmatch.

After all you should have as many rows, as tasks matching with workingdays. SO, you can decide for each row looking for start and end times how much time in this day task was made

ergards

Darek

[#3]

Thanks Dariusz,

yes I have Applicatio_Id and different stages . Each stage has time stamp. Could you please tell how should the expression look like as I am new to Qlikview

[#4]

You are right Dariusz. Basically I want to count hour between Monday to Friday and 9am to 5pm. I have the following expression and it's also calculating correct. The problem is Outliers. Is there any way to exlude top and low 5% entries in calculating average. How can I do for the following expression:

avg(fabs(interval(((Networkdays(Date(EnteredFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date(AnalysedFirstTimestamp,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(EnteredFirstTimestamp))<num('$(vQuitTime)'),if(frac(date(EnteredFirstTimestamp))>num('$(vStartTime)'),Date#(date(floor(EnteredFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(EnteredFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(EnteredFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(EnteredFirstTimestamp),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(AnalysedFirstTimestamp))>num('$(vStartTime)'),if(frac(date(AnalysedFirstTimestamp))<num('$(vQuitTime)'),(Date(AnalysedFirstTimestamp,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(AnalysedFirstTimestamp),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(AnalysedFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(AnalysedFirstTimestamp),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss')))*24