Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
DipeshVadgama
Contributor 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
New Contributor III

Re: Flag if given date month is previous or current month

what it the error message?
DipeshVadgama
Contributor II

Re: Flag if given date month is previous or current month

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

Re: Flag if given date month is previous or current month

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

Re: Flag if given date month is previous or current month

Hi,

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

gn_marvs1989
New Contributor III

Re: Flag if given date month is previous or current month

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

Re: Flag if given date month is previous or current month

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

Re: Flag if given date month is previous or current month

hmmn. check if my QVW will help