Date comparison in set analysis

[tanla]

Hi. I need to create a measure where I need to find a count of issues, where a date field(Datefeild) is in between the dates 1/1/2022 to 4/1/2022.

 I tried something like this, though syntax is correct i am not getting  the desired result. Could someone please help?

Count( {< [Datefeild.autoCalendar.Date] = {">=1/1/2022"} , [Datefeild.autoCalendar.Date] = {"<4/1/2022"}
>} distinct[Issue]).

[rubenmarin · Accepted solution]

Hi, if you use the same field two times in set analysis, only the last will be kept. the syntax for a "between" could be:

Count( {< [Datefeild.autoCalendar.Date] = {">=1/1/2022<4/1/2022"}>} distinct[Issue])

[tanla]

Thank you so much. Looks like it worked. Sorry, I have one more question, I also need to include the dates which are null, how can I do that in the set analysis? 

[rubenmarin]

That could be tricky as null is not a selectable value, maybe with:

Count( {< [Datefeild.autoCalendar.Date] = {">=1/1/2022<4/1/2022"}>+<Issue={"=IsNull([Datefeild.autoCalendar.Date])"}>} distinct[Issue])

[tanla]

Thank you for the reply, when I add the logic for null, I am getting a lot more count than I should be getting. 

[rubenmarin · Accepted solution]

Hi, there are different strategies to select nulls, like assign some dummy date to the nulls like 1900 or 2099, or one date in the ragnge you want to filter, like 1/1/2022.

Or keep the nulls and follow one of these:

- https://community.qlik.com/t5/QlikView-App-Dev/Null-value-in-set-analysis/td-p/1221677

- https://community.qlik.com/t5/QlikView-App-Dev/Set-Analysis-for-Null-and-Not-Null-Values/td-p/922102

-https://community.qlik.com/t5/QlikView-App-Dev/Set-Analysis-include-Null-Values-only/td-p/1579255

Also double check why there are issues without dates and if you really need to count all of them in any date range.