Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
hi,
I am not getting ur ouput
can you give me more explanation
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
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
Hi
the date formats are:
DD/MM/YYYY
I would be looking for a date within the next 3 months.
thanks
Martin
May be try this:
Count({<DateField = {"$('>=' & Date(AddMonths(Today(), -2), 'DD/MM/YYYY'))"}>}ID)
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)
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...
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