Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Try this
=count({$<[StartDate]={"<=$(VStartDate)>=$(VEndDate)"} >} DISTINCT([Worker Full Name]))
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
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.
Thank you for the reply, the text box code was a huge help
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
Not a problem, I am glad I was helpful
Best,
Sunny