topic Re: Networking days in QlikView

Networking days

Thu, 28 May 2015 12:12:30 GMT

Hello Everyone.

I need to calculate the time taken for a ticket to be closed.

I have the startdate and enddate in my table.

I know on excel we can use the Networking days. I can use it on qlikview but the problem is that our company works from 08:00 to 18:00.

networkdays(startdate, enddate)

How do I include the time?

thanks,

Hasvine

Re: Networking days

sunny_talwar — Thu, 28 May 2015 12:15:07 GMT

I may be over simplifying this, but maybe this is what you want:

NetWorkDays(startdate, enddate) * 10

NetWorkDays(startdate, enddate) will calculate the number of work days between the two dates. Multiply it by 10 hours to get the number of hours. Let me know if you are expecting a different solution.

Best,

Sunny

Re: Networking days

swuehl — Thu, 28 May 2015 12:17:42 GMT

If you have timestamps for start and end, date including time information, and want to know the precise duration, have a look at

Calculate hours between two Date/Time strings

Re: Networking days

jonathandienst — Thu, 28 May 2015 12:39:13 GMT

Assuming startdate and enddate are valid QV timestamp values:

=Interval(

RangeMin(18/24, Frac(enddate )) -

RangeMax(8/24, Frac(startdate )) +

RangeMax((NetworkDays(startdate , enddate ) - 1) * (18/24 - 8/24), 0)

, 'd hh:mm')

Set the format string to 'h' or 'd' to see just hours or days respectively/

Re: Networking days

Fri, 29 May 2015 04:58:22 GMT

Hi Jonathan,

Thank you for your reply. Your post is very helpful and the result is correct.

Is it possible in qlikview to count a day as 10 hours instead of 24 hours.

My boss wants the result to show the 10 working hours (08:00 - 18:00) as 1 day.

I managed to do it on excel before but am new to qlikview and I do not really know.

Many Thanks for your precious help.

Kind Regards,

Hasvine

Re: Networking days

jonathandienst — Fri, 29 May 2015 05:13:14 GMT

Like this:

Floor(

(RangeMin(18/24, Frac(enddate )) -

RangeMax(8/24, Frac(startdate )) +

RangeMax((NetworkDays(startdate , enddate ) - 1) * (18/24 - 8/24), 0)) / 10

)

& 'd' &

Interval(

Frac(

(

RangeMin(18/24, Frac(enddate )) -

RangeMax(8/24, Frac(startdate )) +

RangeMax((NetworkDays(startdate , enddate ) - 1) * (18/24 - 8/24), 0)

) / 10

)

, 'hh:mm')

Re: Networking days

Fri, 29 May 2015 05:48:24 GMT

Hi Jonathan,

Thank you for your quick response.

Its works fine.

However, I removed the "/10" for the hour and minutes part and it was the correct result.

I am grateful and thank you for your help.

Kind Regards,

Hasvine