topic Time interval between two timestamps but except closed hours in QlikView

Time interval between two timestamps but except closed hours

Tue, 26 Apr 2011 11:09:05 GMT

Hi I need some help with Time interval between two timestamps. The function give me the interval without any problem

Sum ([SLA Resolution Time]-interval([Resolve Date]-[Open Date],'hh:mm:ss'))

But my problem start when working hour is 0700 - 1800 so is there any easy solution so the time between 18;00;01 and 06;59;59 will not be counted in above function?

Thanks in advance

Time interval between two timestamps but except closed hours

fosuzuki — Fri, 06 May 2011 23:12:43 GMT

Hi Banjir,

could you give an example? Or even better send a sample QVW?

Regards,

Fernando

Re: Time interval between two timestamps but except closed hours

nagaiank — Sat, 07 May 2011 20:56:01 GMT

Hi,

You may take the numerical difference between the two timestamps. Multiply integer part by 11 and fractional part by 24 to get the hours. This assumes that both the timestamps are in the 7:00 - 18:00 window of time. The attached qvw file shows this simple implementation.

If you have to take into account the non-working hours of holidays and weekends, and if you have to check if the timestamps fall in the working hours, some additional development is needed.

Hope this helps.

Time interval between two timestamps but except closed hours

Fri, 13 May 2011 11:44:40 GMT

Hi Banjir1848,

I am also facing this problem.

Can share any sample application realted to this SLA calaculation.

Tthanks,

Re: Time interval between two timestamps but except closed hours

Tue, 21 Jun 2011 11:20:30 GMT

As posted in my own subject;

I have managed to solve the issue, based on an excel example on this site http://chandoo.org/wp/2010/09/10/working-hours-formula/.

The sollution is as follows:

//$1 WorkDayStart. Format 9, 9.5, 21
//$2 WorkDayEnd.Format 9, 9.5, 21
//$3 TimeStampStart. Format DateTime
//$4 TimeStampEnd. Format DateTime

Set fxCalcWorkHours = (($2-$1)*NetWorkDays($3,$4)) -(24*( FMOD($3,1) -($1/24) +($2/24) -FMOD($4,1) ));

So you can load it into a table with the command:

Load *,

$(fxCalcWorkHours(9,18,StartDateTime, EndDateTime) as WorkHoursSpent;

Cheers, Mark

Re: Time interval between two timestamps but except closed hours

Wed, 31 Oct 2012 16:18:17 GMT

Hi Mark,

Can you help me here, i have used your calculation which works great. however some start dates are at weekends, i want to be able to say if the start date is say on a sunday start the calculation at 8am monday to the close date?

can this be done?

thanks

laura