Expression calculation for last 12 months not including this month

[matthewp]

I'm trying to create an expression calculation for last 12 months not including this month.

So for February it would be 2016 FEB to 2017 JAN

I tried

=Count({1<MMMYY ={'>=$(=Date(AddMonths(Today(), -13),'MMM-YY'))<=$(=Date(Today(), 'MMM-YY'))'}>} DISTINCT [MYID])/12

But it didn't work.

[matthewp]

that gives an error

[matthewp]

No, that is for a trigger, i was just sharing that to show what format my date column is in

[sdmech81]

By mistake I put = aftr num..

Replace it with ( n try..Plss attach file if possible..

Sachin

[matthewp]

still an error

[Anonymous]

Hi Matthew ,

Can  you share some sample Application.

Regards

Vinod

[sunny_talwar]

Try this:

=Count({1<MMMYY ={"$(='>=' & Date(AddMonths(MonthStart(Today()), -12), 'YYYYMM') & '<=' & Date(AddMonths(MonthStart(Today()),-0), 'YYYYMM'))"}>} DISTINCT [MYID])/12

[sunny_talwar]

I think the issue was that we were using single quotes around the date format and we had another set of single quotes around our set modifier. That won't work. I changed the outside single quotes to double quotes now. Hopefully it should work now

[matthewp]

Still 0

[sunny_talwar]

Do you have data in your application for Feb 2016 to Jan 2017?

[matthewp · Accepted solution]

Resolved this by doing the expression as:

=ROUND(COUNT({<[MYDATEFIELD]={'$(last12notinccur)'}>} DISTINCT MYID)/12)

and the variable "last12notinccur":

=  '>=' & date(AddMonths(MonthStart(Today()),-12), 'YYYYMM')   & '<=' & date(AddMonths(MonthStart(Today()),-1), 'YYYYMM')