Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
martin_hamilton
Creator
Creator

Select dates 3 months from today' date

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!

9 Replies
sunny_talwar

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.

Not applicable

hi,

I am not getting ur ouput

can you give me more explanation

martin_hamilton
Creator
Creator
Author

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

Anonymous
Not applicable

Hi

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

martin_hamilton
Creator
Creator
Author

Hi

the date formats are:

DD/MM/YYYY

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

thanks

Martin

sunny_talwar

May be try this:

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

Anonymous
Not applicable

Hi

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)

martin_hamilton
Creator
Creator
Author

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...

Anonymous
Not applicable

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