Flag if given date month is previous or current month

[DipeshVadgama]

Hi,

In my script I have given below which give me error.

The following error occurred: Invalid expression

The error occurred here:?

I am not sure why.  Basically I want to check if given date month is previous month or current month.

 If([Date_Field]= 'Closed','Blue',
    If([Date_Field]= 'Cancelled','Brown',
    If([Date_Field]= 'TBC','Green',
    If([Date_Field]= '','Blank Date',
    If((DATE(FLOOR(MonthStart(max([Date_Field]))),'MMM'))= (DATE(FLOOR(MonthStart(max(Today()))),'MMM')),'Amber',
    If((DATE(FLOOR(MonthStart(max([Date_Field]))),'MMM'))> (DATE(FLOOR(MonthStart(max(Today()))),'MMM')),'Green',
    If((DATE(FLOOR(MonthStart(max([Date_Field]))),'MMM'))< (DATE(FLOOR(MonthStart(max(Today()))),'MMM')),'Red'))))))) AS Flag,

[gn_marvs1989]

what it the error message?

[DipeshVadgama]

The following error occurred: Invalid expression

The error occurred here:?

[gn_marvs1989]

Try this :
pick(match([Date_Field],'Closed','Cancelled','TBC','')+1,pick(match(sign( num( Month(Max([Date_Field])) ) - num(Month(Today()) ) ),0,1,-1),'Amber','Green','Red'),'Blue','Brown','Green','Blank Date')

[DipeshVadgama]

Hi,

Thank you, But this also do not work. I am attaching the sample dates files.

[gn_marvs1989]

pick(match([Dates],'Closed','Cancelled','TBC','')+1,pick(match(sign( num( Month([Dates]) ) - num(Month(Today()) ) ),0,1,-1),'Amber','Green','Red'),'Blue','Brown','Green','Blank Date')

[DipeshVadgama]

Its not working. Basically I want to check if month of given dates by user is equal or less or greater than current month.

And also I have careful about year.

[gn_marvs1989]

hmmn. check if my QVW will help