topic Re: Date Convert and select in QlikView

Date Convert and select

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

Hi Community,

I have a table which has date filed , and i am using a master calendar .

The date field i vae converted like this in the script .

Date(Floor(CREATE_DATE),'DD/MM/YYYY') as Date, and then used this to link with the master calendar everything works fine my year , month, weekstart and so on.

And the output for Date appears like the below screenshot . I using the below expression in my text obeject and including the Date range like this which is not working for me

Expression: =Count({$<Date={">=01/09/2020<=30/01/2020"}>}ID) how can change my expression to make the date range pass in this

Thanks in Advance

Re: Date Convert and select

sunny_talwar — Thu, 15 Oct 2020 18:23:31 GMT

@smilingjohn are you may be looking to do this? Greater than 30th Jan 2020 and less than 1 Sep 2020?

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

Re: Date Convert and select

smilingjohn — Thu, 15 Oct 2020 19:20:08 GMT

Hi Sunny this is not working

NO , I wantted to select Sep 1 to 30 sept date range .

Also i have data from 2018 in tihs case how can write this query for each year (12 months )

is therea ways to handle this ?

Re: Date Convert and select

sunny_talwar — Thu, 15 Oct 2020 19:23:57 GMT

@smilingjohn would you be able to elaborate on what isn't working?

Re: Date Convert and select

smilingjohn — Thu, 15 Oct 2020 19:37:05 GMT

Hi Sunny,

What i want is to show the values less than the last day of the month .

like >=01/Sep/2020and <30Sep2020

I have year and month filter depending on selection of the month for given year it should display the data by excluding last day of the monht.

Thanks

Re: Date Convert and select

sunny_talwar — Fri, 16 Oct 2020 11:18:47 GMT

@smilingjohn I am not sure I follow. are you hard coding this or do you want this to be based on selection? Initially you mentioned it using hard coded values, but now you are saying you want it by selection in Month and year. Would you be able to share a sample where we can see this?

Re: Date Convert and select

benvatvandata — Fri, 16 Oct 2020 17:10:28 GMT

For hard coding the dates... have you tried:

=Count({$<Date={">=(=Date('01/09/2020','DD/MM/YYYY'))<=(=Date('30/09/2020','DD/MM/YYYY'))"} >} ID)