Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
DipeshVadgama
Partner - Creator II
Partner - Creator II

Flag if given date month is previous or current month

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,

Labels (2)
7 Replies
gn_marvs1989
Contributor III
Contributor III

what it the error message?
DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

The following error occurred: Invalid expression
The error occurred here:?
gn_marvs1989
Contributor III
Contributor III

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
Partner - Creator II
Partner - Creator II
Author

Hi,

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

gn_marvs1989
Contributor III
Contributor III

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
Partner - Creator II
Partner - Creator II
Author

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
Contributor III
Contributor III

hmmn. check if my QVW will help