Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to get the count of Id's which are having status '2' for the last three months...
Created three variables to get last three months dates as below
vMaxmonth = Date(Max(Month_Date),'DD/MM/YYYY')
vLastmonth = Date(MonthEnd(AddMonths('$(vMaxmonth)',0)),'DD/MM/YYYY')
vLastmonth2 = Date(MonthEnd(AddMonths('$(vMaxmonth)',-1)),'DD/MM/YYYY')
and used these variables in chart like this
Count({$<Month_Date={'$(vMaxmonth)'}+{'$(vLastmonth)'}+{'$(vLastmonth2)'},Status={2}>}ID)
but it is giving me all the records, not the common records having status 2 for last three months,
Anybody please suggest...
Count({$<Month_Date={'$(vMaxmonth)','$(vLastmonth)','$(vLastmonth2)'},Status={2}>}ID)
Try this:
Count({$<Month_Date={"<=$(vMaxmonth) >=$(vLastmonth)"},Status={2}>}ID)
HTH
Sushil
Hi!
Try
IF(Status={2},Count({$<Month_Date={'$(vMaxmonth)'}+{'$(vLastmonth)'}+{'$(vLastmonth2)'},ID))
By Rebeca
Hi,
Try like this
=Count({$<DateFieldName={'>=$(=MonthStart(Max(DateFieldName)))<=$(=MonthEnd(Max(DateFieldName)))'}*
{'>=$(=MonthStart(Max(DateFieldName), -1))<=$(=MonthEnd(Max(DateFieldName), -1))'} *
{'>=$(=MonthStart(Max(DateFieldName), -1))<=$(=MonthEnd(Max(DateFieldName), -1))'}, Status={2}>} ID)
If you need to ID to exist in any of the three months then use this
=Count({$<DateFieldName={'>=$(=MonthStart(Max(DateFieldName)))<=$(=MonthEnd(Max(DateFieldName)))'} +
{'>=$(=MonthStart(Max(DateFieldName), -1))<=$(=MonthEnd(Max(DateFieldName), -1))'} +
{'>=$(=MonthStart(Max(DateFieldName), -1))<=$(=MonthEnd(Max(DateFieldName), -1))'}, Status={2}>} ID)
Hope this helps you.
Regards,
Jagan.
Hi,
Try this:
vMaxmonth (the same)
vLast2month = Date(MonthStart(AddMonths('$(vMaxmonth)',-2)),'DD/MM/YYYY')
Count({$<Month_Date={">=$(vLast2month)<=$(vMaxmonth)"},Status={2}>}ID)
Try this expression:
if(inyear('vMaxmonth','vLastmonth2',0) and status='2',count(ID))