topic Re: Calculation of networkdays in Qlik Learning Discussions

Calculation of networkdays

Anonymous — Wed, 24 Dec 2014 08:44:16 GMT

Can anybody please help urgently?

Need a simple solution

I want to calcuate the elapsed days i.e. networkdays between date raised and resolved date.

Simply using networkdays() doesnt give the right answer since some of the rows in raised and resolved date columns are right, center or even left aligned.

have tried the below query but doesnt seem to work for all the rows.

if(date([Resolved Date],'M/D/YYYY') > '', NetWorkDays(date([Date Raised],'M/D/YYYY'),date([Resolved Date],'M/D/YYYY')),NetWorkDays(date([Date Raised],'M/D/YYYY'), date(today(),'M/D/YYYY') )) as Elapsed_Days,

Re: Calculation of networkdays

Gysbert_Wassenaar — Wed, 24 Dec 2014 08:49:32 GMT

since some of the rows in raised and resolved date columns are right, center or even left aligned.

That means some of your dates are not dates, but text strings. You have to convert these in the script in real dates. You can use the alt function to capture the dates than aren't numeric dates already and convert them into dates using the date# function:

LOAD

date(alt(MyDate,date#(MyDate,'M/D/YYYY'),'M/D/YYYY') as MyDate,

...other fields....

FROM ...somewhere...

See this blog post for more information: Get the Dates Right

Re: Calculation of networkdays

Anonymous — Fri, 26 Dec 2014 06:07:45 GMT

Have tried this format to calculate networkdays() but it doesnt seem to be working . Is there any incorrect in my query

if(date(alt([Resolved Date],Date#([Resolved Date],'M/D/YYYY'),'M/D/YYYY'))>'', NetWorkDays(date(alt([Resolved Date],Date#([Resolved Date],'M/D/YYYY'),'M/D/YYYY')),date(alt([Date Raised],Date#([Date Raised],'M/D/YYYY'),'M/D/YYYY'))),
NetWorkDays(today(),date(alt([Date Raised],Date#([Date Raised],'M/D/YYYY'),'M/D/YYYY')))

Re: Calculation of networkdays

Anonymous — Fri, 23 Jan 2015 10:09:45 GMT

Hi Gysbert

There was a problem with the source data.I had to correct the way it was being exported. The query works fine now