Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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]))
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,
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]))
Thanks Lauri. That works great!