Working hours only

[#1]

Hi All,

I have to 2 timestamp fields Start Date and Closed Date. I want to calculate the time it takes from start date to close date.

i only want to include times between 8am and 6 pm and not including saturdays and sundays.

for example.

Start date is 30/10/2012 15:00:00 and Closed Date is 31/10/2012 10:00:00 i would expect the calculated to bring back 5 hours as the cycle time.

Can anyone help me work out how to do this calculation in Qlikview?

Thanks

Laura

[#2]

Hi Stefan,

Thanks for your help it has been really useful. I have got the expression working in the chart. However i have noticed it has dropped records where the start and end time are both at a weekend. i would expect it to bring back a 0. Also it has dropped records where a case is still open and only has a start date. i would just want it to return a blank in the table.

Any ideas?

Thanks

Laura

[#3]

Hi Stefan,

Thanks for your help it has been really useful. I have got the expression working in the chart. However i have noticed it has dropped records where the start and end time are both at a weekend. i would expect it to bring back a 0. Also it has dropped records where a case is still open and only has a start date. i would just want it to return a blank in the table.

Any ideas?

Thanks

Laura

[swuehl]

Regarding the dropped records: The expression will return zero, these lines will be hidden if 'suppress zero values' is checked in presentation tab of your chart. Uncheck this option.

Regarding the records with only start date: You can add an if() statement to check if your end date is set:

If( len(trim(case_closedate)),

.... // here comes the interval calculation

, ' ')

[#5]

Hi i have got this working now which is great thanks very much for your help.

My new issue is that for example

Start Date                               End Date                               Calculationof working hours

09.02.12 09:54:39            11.02.2011 09:14:49                    19:20:09

This is the correct answer, however i want it to return that it took 1 day 9hrs and 20mins and 09 seconds.

Thanks

Laura

[swuehl]

You could try something like

=floor(WorkingHours / maketime(10)) &' days '

& hour( WorkingHours-floor(WorkingHours,maketime(10)))&' hrs '

& Minute(WorkingHours-floor(WorkingHours,maketime(10))) &' mins and '

& second(WorkingHours-floor(WorkingHours,maketime(10))) &' seconds'

[#7]

Thats Great thanks very much for your help

[#8]

With regards to holidays i see you have called a holiday '41130' do dates have to go in this format?

or can i simply set holidays as '25/12/2012', 26/12/2012, '01/01/2013' etc

[swuehl]

If that's your standard date format, it should work (41130 is just the numerical representation of  8/9/12).

Set DateFormat = 'DD/MM/YYYY';

Set vHol = '25/12/2012', '26/12/2012', '01/01/2013';

Note that I included each date into single quotes.

Check also

http://community.qlik.com/message/131162

on how to load the holidays from a table.

[Anonymous]

Hi, What changes I would need if my total work hours are 20(1:00:00 AM to 22:00 PM)? I tried making the below changes but it didn't work:

NetWorkDays(Created+1,[ACK Date]-1) * MakeTime(20) 
// 20 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays([ACK Date],[ACK Date]),Rangemin(rangemax(frac([ACK Date]),maketime(1)),maketime(22))-Rangemax(rangemin(frac([ACK Date]),maketime(1)),maketime(1)),0)
// working hours last day
,if(NetWorkDays(Created,Created),Rangemin(rangemax(frac(Created),maketime(22)),maketime(22))-Rangemax(rangemin(frac(Created),maketime(22)),maketime(1)),0) // working hours first day
,if(NetWorkDays(Created,Created) and floor(Created)=floor([ACK Date]),-MakeTime(20))
// correct for first equals last day
)
)
as RspHrs

Please advise. The 2 dates we use are Ack date(DT2) and Created(DT1)

[ajayvermaida]

@swuehl 

Hi Kush,

can you please help me to calculating the time diff between two dates based on given points

1- First I have to calculate the time weekdays in between 7 Am to 11 PM excluding sat and sun . am using below script for this but prob is that when both the dates are weekend then its also calculate the time diff  .

if(not WildMatch(IssueStatus,'Pending') , (Interval((RangeMin(frac(IssueResolutionDate), MakeTime(23))- RangeMax(frac(IssueCreateDate), MakeTime(7)))+
( NetWorkDays(IssueCreateDate, IssueResolutionDate-1) * MakeTime(16)) ))) as TotalTime,

2- Second I have to calculate the time diff only Weekend Sat or Sun in between 10 Am to 4 PM . also if any dates is weekend and other one is weekday then it calculate the time based on diff time difference .

3- if any ticket open in the last month and closed in next month then it calculate the time as point 1 except if it comes on weekdays .

Please help me to find the correct result