Count based on date range

[#1]

Hi All,

I am having dataset

ID	Date
A	15/10/2013
B	14/10/2013
C	2/9/2013
D	4/5/2012
E	7/6/2012

I need the count of id's if it coming in last 15 months from today

For eg in this case my output will be count(ID) = 3 (i.e. A ,B and C)

[jagan]

Hi,

Try this expreesions

For last 15 months

=Sum({<Date={'>=$(=AddMonths(Max(Date), -15))<=$(=Date(Max(Date)))'}>} ID)

Before doing this please check the date formats are correct in Date dimension and =AddMonths(Max(Date), -15), if not format the date like below

=Sum({<Date={">=$(=Date(AddMonths(Max(Date), -15), 'D/M/YYYY')<=$(=Date(Max(Date), 'D/M/YYYY'))"}>} ID)

Hope this helps you.

Regards,

Jagan.

[#3]

Hi Jagan,

Thanks for your reply.

I tried this syntax but it is giving me blank

=count({<[Service Header.Completion Date]={">=$(=Date(AddMonths(Max([Service Header.Completion Date]), -15),'D/M/YYYY')<=$(=Date(today(), 'D/M/YYYY'))"}>} Key_VehicleRetention)

[senpradip007 · Accepted solution]

try this

Count({< Date = {">=$(=AddMonths(Today(), -15))"} >} ID)

Hope it will help.

[jagan]

Hi,

Try this expression

=Count({<Date={">=$(=Date(AddMonths(Today(), -15), 'D/M/YYYY'))<=$(=Date(Today(), 'D/M/YYYY'))"}>} ID)

Also please find attached file it is working there.

Regards,

Jagan.

[brijesh1991]

Hey use this expression; it's working in my dashboard.

Count({<Date ={">=$(=MonthStart(max(Date),-14))<=$(=max(Date))"},Year=,month=,Quarter=>}ID)

[Anonymous]

Hi,

in your script do this

ID,

Date(Date,'DD/MM/YYYY') AS Date

from abc<>;

then use expression

=Count({< Date = {">=$(=AddMonths(Today(), -15))"} >} ID)

you will get answer 3

Regards,

Anant

[Anonymous]

Please check with latest that i edited.

[#9]

First, please create the flag in the table like below:

Let vBaseDate = Num(AddMonths(Today(), -15));

LOAD *,

          IF(Floor([Completion Date]) > = $(vBaseDate) , 1 ,0) AS 15M_FLAG;

On the UI, please use the expression:  COUNT({<15M_FLAG={1}>} Key_VehicleRetention)

[#10]

Thanks it is working fine