topic Re: Select dates 3 months from today' date in QlikView

Select dates 3 months from today' date

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

Hi - i am creating a text object/kpi object and wish to count the number of records which have a date associated which is within 2 months of todays date

e.g.

10/08/2015

11/12/2016

10/08/2016

10/09/2016

11/09/2016

I would expect it to filter the following dates:

10/08/2016

10/09/2016

11/09/2016

I am ok deriving a new date from the original date but cant figure this one out!

Thanks!

Re: Select dates 3 months from today' date

sunny_talwar — Wed, 10 Aug 2016 14:36:23 GMT

I would suggest using a set analysis here. If say your current expression for KPI is Sum(Sales), you can not try this:

Sum({<DateField = {"$('>=' & Date(AddMonths(Today(), -2), 'DD/MM/YYYY'))"}>}Sales)

I am not sure if you date is DD/MM/YYYY or MM/DD/YYYY, but use the right format in the above expression.

Re: Select dates 3 months from today' date

Wed, 10 Aug 2016 14:36:47 GMT

hi,

I am not getting ur ouput

can you give me more explanation

Re: Select dates 3 months from today' date

martin_hamilton — Wed, 10 Aug 2016 14:46:28 GMT

Hi Sunny

I am actually just after a count of records where the dates match the selection criteria i.e. within the next 2 months.

Would a COUNT work here?

The file is keyed by an unique ID.

I think this will work but am in the process of testing it:

=Count([Contract End Date])>=Today() -2

Re: Select dates 3 months from today' date

Anonymous — Wed, 10 Aug 2016 14:47:52 GMT

Do you need NEXT three months or PREVIOUS three months ?

FYI : I can not analyse your date formats ;;; DD/MM/YYYY or MM/DD/YYYY

Re: Select dates 3 months from today' date

martin_hamilton — Wed, 10 Aug 2016 14:52:57 GMT

the date formats are:

DD/MM/YYYY

I would be looking for a date within the next 3 months.

thanks

Martin

Re: Select dates 3 months from today' date

sunny_talwar — Wed, 10 Aug 2016 15:10:55 GMT

May be try this:

Count({<DateField = {"$('>=' & Date(AddMonths(Today(), -2), 'DD/MM/YYYY'))"}>}ID)

Re: Select dates 3 months from today' date

Anonymous — Wed, 10 Aug 2016 15:34:26 GMT

If you are looking for within next 2 months , try this

=count({<DateField = {">=$(=Date(Today(),'DD/MM/YYYY')))<=$(=Date(AddMonths(Today(), 2),'DD/MM/YYYY'))"}} ID)

Re: Select dates 3 months from today' date

martin_hamilton — Wed, 10 Aug 2016 15:49:45 GMT

Hmmm thats not working either;

My file looks like the following:

Subscription Number, Contract End Date

1, 10/07/2016

2, 10/08/2016

3, 10/08/2016

So i would expect a return count of 2...

Re: Select dates 3 months from today' date

Anonymous — Thu, 11 Aug 2016 11:33:07 GMT

try this

=count({<DateField = {">=$(=Date(Today(),'DD/MM/YYYY')))<=$(=Date(AddMonths(Today(), 2),'DD/MM/YYYY'))"}} [Subscription Number])

and try to add some more data !!!? as you need to CALCULATE next 2 months !!

Subscription Number, Contract End Date

1, 10/07/2016

2, 10/08/2016

3, 10/08/2016

4. 25/08/2016

5. 28/08/2016