topic Date range in QlikView

Date range

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

Re: Date range

Chanty4u — Thu, 15 Oct 2020 12:25:58 GMT

try something like this?

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

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

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

Re: Date range

smilingjohn — Thu, 15 Oct 2020 14:35:57 GMT

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 .

Re: Date range

Chanty4u — Thu, 15 Oct 2020 14:55:57 GMT

Not sure.

is count getting wrong?

can you attach sample excel file?

Re: Date range

smilingjohn — Thu, 15 Oct 2020 14:54:20 GMT

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

Re: Date range

Chanty4u — Thu, 15 Oct 2020 15:09:31 GMT

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

Re: Date range

smilingjohn — Thu, 15 Oct 2020 15:58:23 GMT

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

Re: Date range

smilingjohn — Thu, 15 Oct 2020 16:47:19 GMT

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 ?

Re: Date range

Brett_Bleess — Tue, 27 Oct 2020 18:13:12 GMT

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