count of records within date range

[Aag]

Hello, Can someone help me what is wrong with the below calc?

I need to get distinct count of products between 2 time periods.

COUNT(distinct{<date1={">2020-11-01 <= 2020-11-20"}>}PRODUCTS)

[GaryGiles]

Try this:

count(distinct {$<Date1={">$(=Date('2020/11/01'))<=$(=Date('2020/11/20'))"}>} PRODUCTS)

[Aag]

Thank you @GaryGiles  I tried this.. It does not throw error, but get the result as 0 always with this condition.. 😞 

[GaryGiles]

I'm not sure how your dates are setup is Date1 a true date field, you might want to try it with month first:

count(distinct {$<Date1={">$(=Date('11/01/2020'))<=$(=Date('11/20/2020'))"}>} PRODUCTS)

[MayilVahanan · Accepted solution]

Hi @Aag 

What is your date format?

COUNT(distinct{<date1={">2020-11-01 <= 2020-11-20"}>}PRODUCTS)

2020-11-01 its must be same as ur date1 format. And also, Date1 is in date format or text format? if its text format, then convert into date format via script

[Aag]

Hi @Anonymous  Yes the date might be the issue.. but not sure what mistake I am doing.. 

In the script I gave 

Date(Date#(Salesdt,'YYYY/MM/DD')) as Salesdt, 

Option1 : In the measure I gave.. 

COUNT(distinct{<Salesdt={">2020-11-01 <=2020-11-22"}>}PRODUCT)

It still does not work.. I tried another way..

Option2:

Declared as variables..  

Let vStartDate = Date(Date#('2020-11-01','YYYY/MM/DD'));
Let vEndDate = Date(Date#('2020-11-22','YYYY/MM/DD'));

COUNT(distinct{<Salesdt={">vStartDate <=vEndDate "}>}PRODUCT)

Both options are not working.. Maybe it is still date format that I am doing incorrect.. Can you advise? 

[Aag]

Appreciate if anyone has suggestions why my set analysis conditions are not working..

[Aag]

Thanks @MayilVahanan  I figured out the issue.

Now it works. Thank you