How can I achieve the common data for the last three months

[#1]

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

[manideep78]

Count({$<Month_Date={'$(vMaxmonth)','$(vLastmonth)','$(vLastmonth2)'},Status={2}>}ID)

[sushil353]

Try this:

Count({$<Month_Date={"<=$(vMaxmonth) >=$(vLastmonth)"},Status={2}>}ID)

HTH

Sushil

[#4]

Hi!

Try

IF(Status={2},Count({$<Month_Date={'$(vMaxmonth)'}+{'$(vLastmonth)'}+{'$(vLastmonth2)'},ID))

By Rebeca

[jagan]

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.

[jvitantonio]

Hi,

Try this:

vMaxmonth (the same)

vLast2month = Date(MonthStart(AddMonths('$(vMaxmonth)',-2)),'DD/MM/YYYY')

Count({$<Month_Date={">=$(vLast2month)<=$(vMaxmonth)"},Status={2}>}ID)

[#7]

Try this expression:

if(inyear('vMaxmonth','vLastmonth2',0) and status='2',count(ID))