Defauly selection trigger - previous + current year minus current month

[matthewp]

I want to create a sheet trigger to select the previous year and the current year but not the current month.

Sample data is as follows

Year month

201511

201512

201601

201602

201603

201604

Selection I would want would be

201511

201512

201601

201602

201603

[sunny_talwar]

What is the logic here to exclude everything before 201412? Only show this year (except current month) and complete previous year?

I would recommend changing number to date using date#() function in the  script

[#3]

In the trigger,

use select in field

Field : Year month

Search String : ='<=' & Date(max([Year month]),'YYYYMM')

Regards,

Anjali Gupta

[matthewp]

yeh thats the logic

i was thinking..

='<' & Max([Year period]) & > left(Max([Year period]), 4) - 2

[sunny_talwar · Accepted solution]

Try this:

='<' & Max(YearMonth) & '>=' & Num#(Date(YearStart(Max(Date#(YearMonth, 'YYYYMM')), -1), 'YYYYMM'), '##')

[matthewp]

How would i do this for actual dates?

Month

Jan-15

Feb-15 etc

same logic

i tried ='<' & Max(Month) & '>=' =year(Month)-2

[sunny_talwar]

Something like this:

='<' & Date(Max(Month), 'MMM-YY') & '>=' & Date(YearStart(Max(Month), -1), 'MMM-YY'), '##')

[matthewp]

Something missing i think

Garbage after expression','

='<' & Date(Max(Month), 'MMM-YY') & '>=' & Date(YearStart(Max(Month), -1), 'MMM-YY'), '##'

[sunny_talwar]

My bad, just this:

='<' & Date(Max(Month), 'MMM-YY') & '>=' & Date(YearStart(Max(Month), -1), 'MMM-YY')