Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
samantha92
New Contributor III

NetWorkDays and count if expression

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.

1 Solution

Accepted Solutions
samantha92
New Contributor III

Re: NetWorkDays and count if expression

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

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')<=30, '11-30 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')>=31 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')<=35, '31-35 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')>=36 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')<=45, '36-45 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')>=46, '46+ days',

)))))

View solution in original post

3 Replies
MVP & Luminary
MVP & Luminary

Re: NetWorkDays and count if expression

Perhaps like this:


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


talk is cheap, supply exceeds demand
samantha92
New Contributor III

Re: NetWorkDays and count if expression

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

samantha92
New Contributor III

Re: NetWorkDays and count if expression

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

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')<=30, '11-30 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')>=31 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')<=35, '31-35 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')>=36 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')<=45, '36-45 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')>=46, '46+ days',

)))))

View solution in original post