Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

sanjyotpatkar
Contributor II

Calculation of networkdays


@

Hi

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,

3 Replies

Re: Calculation of networkdays

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



talk is cheap, supply exceeds demand
sanjyotpatkar
Contributor II

Re: Calculation of networkdays

Hi

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')))

sanjyotpatkar
Contributor II

Re: Calculation of networkdays

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

Community Browser