Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')
Is the format MMM-YY or MMMYY?
YYYYMM
Then give this a try
=Count({1<MMMYY ={'>=$(=Date(AddMonths(Today(), -13),'YYYYMM'))<$(=Date(MonthStart(Today()), 'YYYYMM'))'}>} DISTINCT [MYID])/12
Note: I added MonthStart to upper condition and changed from <= to <
hmm that gives me 0
Is there a space between YYYY MM or is it YYYYMM? If there is space, may be this
=Count({1<MMMYY ={'>=$(=Date(AddMonths(Today(), -13),'YYYY MM'))<$(=Date(MonthStart(Today()), 'YYYY MM'))'}>} DISTINCT [MYID])/12
Still 0,
no space between the dates.
If this helps, i have a trigger on the same sheet that uses
= '>=' & date(AddMonths(MonthStart(Today()),-12), 'YYYYMM') & '<=' & date(AddMonths(MonthStart(Today()),-0), 'YYYYMM')
Why do you need 0 for second Equation, May be this?
= '>=' & date(AddMonths(MonthStart(Today()),-12), 'YYYYMM') & '<=' & date(MonthStart(Today()), 'YYYYMM')
Plss attach application..