topic IF AND between two dates in QlikView

IF AND between two dates

Thu, 25 Jun 2015 12:02:17 GMT

Hello

I am trying to get the new starters between two set dates

I have the last day in the range: VStartDate

I have the first day in the range: VEndDate

(I know they are backwards, but the variables are used elsewhere and it makes sense, trust me)

So what I want to do is count the workers with a start date between these two dates

=count({$<[StartDate]={"<=$(VStartDate)"},[StartDate]={">=$(VEndDate)"} >} DISTINCT([Worker Full Name]))

What I believe I have is, count all start dates with a start date less than or equal to the end of the month, and greater than or equal to the beginning of the month.

However it is returning dates outside the range. Is there a problem with the formula? The only thing I can think is because I have two conditionals on the same field, but I am not 100% on how to get multi conditional in one field

Thank you for any help you can offer

Re: IF AND between two dates

Thu, 25 Jun 2015 12:07:25 GMT

Try this

=count({$<[StartDate]={"<=$(VStartDate)>=$(VEndDate)"} >} DISTINCT([Worker Full Name]))

Re: IF AND between two dates

sunny_talwar — Thu, 25 Jun 2015 12:08:19 GMT

Try this:

=Count({$<[StartDate]={"$(='<=' & $(VStartDate) & '>=' & $(VEndDate))"} >} DISTINCT([Worker Full Name]))

and make sure that this expression ='<=' & $(VStartDate) & '>=' & $(VEndDate) in a text box gives you the date range you are looking for.

HTH

Best,

Sunny

Re: IF AND between two dates

swuehl — Thu, 25 Jun 2015 12:09:15 GMT

Don't use the same field within one set modifier twice, QV will only consider one element list, I believe.

=count({$<[StartDate]={">=$(VStartDate)<=$(VEndDate)"} >} DISTINCT([Worker Full Name]))

Note that I also reversed the comparison operators, this is how I read your requirements.

Re: IF AND between two dates

Thu, 25 Jun 2015 12:13:00 GMT

Thank you for the reply, the text box code was a huge help

Re: IF AND between two dates

Thu, 25 Jun 2015 12:13:39 GMT

Thank you, I did have to change the operators back, as my start and end are backwards (confusing I know)

Other than that worked, thank you

Re: IF AND between two dates

sunny_talwar — Thu, 25 Jun 2015 12:14:09 GMT

Not a problem, I am glad I was helpful

Best,

Sunny