How to count the IDs with condition including AND and OR

[zhj0735]

Hi I would like to count numbers of vehicle in stock by using two data columns "in_service" and "out_service".

If the vehicle is still in use at this point, then the "out_service" would be null. I want to count those still in service based on the data: 

=Count( {< [in_service_date.autoCalendar.YearMonth]={"<=$(YRMON)"},[out_service_date.autoCalendar.YearMonth]={">=$(YRMON_)"}|IsNull(out_service_date)> } >} [vin])

However, when I use the expression to edit my counts, there is always error showing "error in expression Error in set modifier expression". Not sure how am I gonna be able to make it right? I thought that is very straightforward...

[BrunPierre]

Try

=Count({<[in_service_date.autoCalendar.YearMonth]={"<=$(YRMON)"},[out_service_date.autoCalendar.YearMonth]={">=$(YRMON_)"},out_service_date={"$(=IsNull(out_service_date))"}>} [vin])

[zhj0735]

Thank you BrunPierre. However, I dont think it works as the last two conditions contradict:

out_service_date.autoCalendar.YearMonth]={">=$(YRMON)"} and 

out_service_date={"$(=IsNull(out_service_date))"}>} [vin]

As long as it has value, it wont be null. That is the OR condition that I want to implement as I want to count either has later time of out_service_date, or have not been out of service yet (meaning it has NULL value).

Thanks! Look forward to other solutions.

[maheshkuttappa · Accepted solution]

This Should work

=Count({<[in_service_date.autoCalendar.YearMonth]={"<=$(YRMON)"},[out_service_date.autoCalendar.YearMonth]={">=$(YRMON_)"} >+ <out_service_date={"$(=IsNull(out_service_date))"}>} [vin]) 

[BrunPierre]

I forgot to include the operator for combining the two sets, which @zhj0735 has already fixed that.

[zhj0735]

Thanks Maheshkuttappa! The + seems working. Wondering where can I find the correct answer like what you have provided myself from the help instruction or anything? The operation provided by the help mode told me to use "|" but I dont know why it did not work.

Thanks!

[maheshkuttappa]

This will be good resource 

https://community.qlik.com/t5/Support-Updates/Working-with-Set-Analysis-and-Set-Expressions/ba-p/179...

[zhj0735]

That is very helpful. Thanks for sharing!