topic Re: NetWorkDays calculation with add'l logic? in QlikView

NetWorkDays calculation with add'l logic?

Mon, 25 May 2015 14:37:24 GMT

I haven't found anything in documentation pdfs or posts that address my current assignment, so I'm asking here. These are the things I'm working with:

Calculate the open time for requests using the formula ReloadTime() - incident.open
If the SEV field is a "3" or "4", do NOT count open time during Saturday and Sunday
A request can be submitted at any time during the week Sun-Sat, and sometimes can be open for more than 8 days.
Results should be in dd:hh:mm format

I can get results using NetWorkDays in conjunction with Frac,Interval,Time, etc as suggested in other posts on here but it's never accurate; and I'm figuring it's because of the fact that requests can be opened during the weekend.

Thank you for your assistance.

Re: NetWorkDays calculation with add'l logic?

avinashelite — Mon, 25 May 2015 14:43:33 GMT

try like this:

if(SEV=3 or SEV=4, networkingdays(ReloadTime() - incident.open), ReloadTime() - incident.open)

Re: NetWorkDays calculation with add'l logic?

Mon, 25 May 2015 15:33:34 GMT

Thank you for your prompt reply, Avinash, but your proposed expression is missing some things I need:

While it addresses the SEV constraints in calculation, I still need something that accounts for requests made during the weekend as well as the dd:hh:mm format
"networkingdays" doesn't seem to be recognized by QV. NetWorkDays() is but it uses different syntax than your expression

Any other suggestions?

Re: NetWorkDays calculation with add'l logic?

maxgro — Mon, 25 May 2015 18:00:12 GMT

try if this (and Gysbert's link) can help you

Re: Calculate Elapsed Time Nett

Re: NetWorkDays calculation with add'l logic?

Mon, 25 May 2015 20:05:08 GMT

The one discussion you sent me about "Calculate hours between two Date/Time strings" had something I was looking for - simplicity. Particularly this post here:

=Interval(

(RangeMin(frac(Closedate), MakeTime(18))

- RangeMax(frac(Logdate), MakeTime(8)))

+ (NetWorkDays(Logdate, Closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)

Just an expresssion using what QV has already. No calendar tables, nothing fancy. Fortunately, I don't need such granularity for the business hours so I don't think I need the MakeTime part. This expression works unless the request is open through the weekend, which isn't what I need:

if(num(WeekDay(incident.sys_created_on))>4,

1-NetWorkDays(timestamp(incident.sys_created_on),timestamp(ReloadTime())) + Frac(timestamp(incident.sys_created_on)-Timestamp(ReloadTime(), 'hh:mm'))

NetWorkDays(timestamp(incident.sys_created_on),timestamp(ReloadTime())) + Frac(timestamp(incident.sys_created_on)-Timestamp(ReloadTime(), 'hh:mm')))

Any further help is appreciated!

Re: NetWorkDays calculation with add'l logic?

avinashelite — Tue, 26 May 2015 05:20:29 GMT

Hi David,

Can you please share your app??

Re: NetWorkDays calculation with add'l logic?

Tue, 26 May 2015 14:19:50 GMT

Sure, here's the trial app I've been working on.