Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
try this
Count({< Date = {">=$(=AddMonths(Today(), -15))"} >} ID)
Hope it will help.
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.
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)
try this
Count({< Date = {">=$(=AddMonths(Today(), -15))"} >} ID)
Hope it will help.
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.
Hey use this expression; it's working in my dashboard.
Count({<Date ={">=$(=MonthStart(max(Date),-14))<=$(=max(Date))"},Year=,month=,Quarter=>}ID)
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
Please check with latest that i edited.
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)
Thanks it is working fine