topic Re: NetWorkDays and count if expression in QlikView

NetWorkDays and count if expression

samantha92 — Wed, 11 May 2016 15:45:57 GMT

Hi guys,

I need to create an expression to count how many assessments are within a range of amount of days open:

0-10 days
11-30 days
31-35 days
36-45 days
46+ days

At the moment I am calculating this like so:

=Count(if(Today()-AssessmentStartDate>=0 and Today()-AssessmentStartDate<=10, AssessmentStartDate))

This gives me a total of 77 assessments for 0-10 days open range.

However, this total includes weekends and bank holidays, which I want to exclude from this calculation. I am using the NetWorkDays function to try and exclude those dates I do not want, from which I have created the following expression:

=NetWorkDays(AssessmentStartDate,Today(), '24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')

When I try and incooporate the two expressions together it seems to just cancel one expression out (depending on which one I put first) and I am still left with 77 assessments:

=Count(if(Today()-AssessmentStartDate>=0 and Today()-AssessmentStartDate<=10, AssessmentStartDate and NetWorkDays(AssessmentStartDate,Today(), '24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')))

Is there a better way of being able to make this work so that I can exclude weekends and bank holidays whilst doing a count if the amount of days calculated fall within each of the ranges?

Any help would be appreciated, thanks.

Re: NetWorkDays and count if expression

Gysbert_Wassenaar — Wed, 11 May 2016 16:35:21 GMT

Perhaps like this:

=Count({<AssessmentStartDate={">=$(=Date(Today()-10))<=$(=Date(Today()))"}*{"=WeekDay(AssessmentStartDate)>=5"}>} AssessmentStartDate)

Re: NetWorkDays and count if expression

samantha92 — Thu, 12 May 2016 07:03:48 GMT

Thanks or the reply, but this just gives me 0?

Am I best hard coding the holidays I want to exclude into my load script instead of using them in my expressions?

**EDIT**

If I load this into my script am I able to create a variable for the NetWorkDays and holidays and then create an expression using them?

LOAD

if(Today()-AssessmentStartDate >=0 and Today()-AssessmentStartDate <=10, '0 - 10 days') AS RangeOne,

if(Today()-AssessmentStartDate >=11 and Today()-AssessmentStartDate <=30, '11 - 30 days') AS RangeTwo,

And then creating a variable like so:

=NetWorkDays(AssessmentStartDate,Today(), '24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')

And then doing something along the lines of this:

=Count(if(RangeOne-vNetWorkDays,RangeOne))

Re: NetWorkDays and count if expression

samantha92 — Thu, 12 May 2016 09:54:41 GMT

It's ok I'ev figured it out, pretty big expression but it gives me my results I'm after

=if(NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')>=0 and

NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')<=10, '0-10 days',

if(NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')>=11 and

)))))