Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

IF AND between two dates

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: IF AND between two dates

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.

6 Replies
Not applicable

Re: IF AND between two dates

Try this

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

Re: IF AND between two dates

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

MVP
MVP

Re: IF AND between two dates

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.

Not applicable

Re: IF AND between two dates

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

Not applicable

Re: IF AND between two dates

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

Not a problem, I am glad I was helpful

Best,

Sunny