Date range

[smilingjohn]

Hi All,

I have a date format like belwo screenshot . I trying to use a  text object to display the count of ID's between the date range . 

How do i write my expression for this in a set analysis ? 

Count of ({<Date>={'01/01/205 and <=31/01/2005'}>}ID)  . Can please hel me on this ?

[Chanty4u]

try something like this?

count({ $<Date={'>01/01/205<31/01/2005'}>}  ID)

or

count({ $<Date={'>01/01/205<31/01/2005'}>} distinct ID)

or

Count({$<Date = {">=01/01/205<=31/01/2005"}>} DISTINCT  ID)

change your date format in script like below .

Date(Yourdatefield, 'DD/MM/YYYY') as Yourdate

[smilingjohn]

Thanks for the reply @Chanty4u 

Third query works but this is not giving me the proper results . 

Is the any other way to select the date witout converting it as shown in the screenshot . 

[Chanty4u]

Not sure.

is count getting wrong?  

can you attach sample excel file?

[smilingjohn]

Hi Chanty4U, 

Your 3rd expression works properly . 

Now if I give the range of dates within set analysis  the numbers are matching , if i just conevrt to month from the Date then numbers are nit matching . 

How can i make this function to work for all the month selction becuse some months will have 30 days and some will have 31 days . 

I ahvea master calendar too . 

Thanks

[Chanty4u]

Even if the month or date .. it will show that perticular date and month range will show same count .

im not sure. how it is not matching.   

Can you attach any sample 

[smilingjohn]

Hi Chany4u

Please llo at the screenshot below  , in the month of September by defualt selection will consider all the 31 days , 

but when we add the date range from >=01/09/2020<=30/09/2020 then it is considering till 29th september . 

and the same result does the users have . thats the reason i am really curious to know how can i assign date range for all the month in the script  to achive this ?Thanks 

[smilingjohn]

I have master Calendar in my real time and I have converted the my Date to in the script and then master calendar. 

Date(Floor(Date,'DD/MM/YYYY') as Date 

after which the below query is not working and shoing some other values "

Expression : 

count({$<Date={">=01/09/2020<=30/09/2020"}>}ID)

Do i need to alter the Dates table ?

[Brett_Bleess]

I would think this most likely has to do with potentially needing to AGGR something as well, here is a Design Blog post on that, hopefully that may help you figure things out.

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett