Count the number of records(ID) given three columns of date

[zhj0735]

I have a dataset of ID and 3 dates (input_date, output_date, and sold_date). And I want to count the number of ID based on 3 different dates  that lies in the time period counted as stock, with my logic being:

=count(if (input_date.autoCalendar.YearMonth='<=$(YRMON)' and output_date.autoCalendar.YearMonth='>$(end of YRMON)' ,[ID]))

However, I ran into problems about how could I define a YRMON that can be used to compare those two dates. I tried creating a master dimension YRMON and realize they are not able to be used in the expression.

I know it is doable by creating such a table in python and visualize it in Qlik Sense, but wondering what is the most efficient way to finish this time of data analysis inside of Qlik Sense? 

Thanks all!

[zhj0735]

I import another table with the dates. Now the YRMON can be compared to independently. But wondering how am I be able to compare the dates?

I am using: Count( {< [out_service_date.autoCalendar.Date]={">=(end_dt.autoCalendar.Date)"} >} distinct vin)

But it doesnt work.

Thanks,

[Lauri · Accepted solution]

Depending on how many rows of data you have (millions?), the IF method is simplest, and I would write it like this with the date itself (I assume these 3 fields are dates because Qlik created autocalendar fields from them):

=count(if (input_date<=end_dt and output_date >end_dt, [ID]))

[zhj0735]

Thanks Lauri. That works great!