how to fix syntax error when nested if statement are used in set expression?

[alex_202]

I am trying to put multiple if statements in my set expression, and in my case I need to use AND to concatenate them to evaluate together. But I got syntax error which I can't figure it out. Can anyone help me what went wrong in my set expression below:

AVG(IF(type_cd='py' AND res_cd='ls'
AND match(le_id, '01','03')
AND match(cm_cd, 'ct','at')
AND yr <= Year(MonthEnd(AddMonths(Date(Now()),-1)))
AND mnth <= if(YEAR_NO <=Year(MonthEnd(AddMonths(Date(Now()),-1))), Month(MonthEnd(AddMonths(Date(Now()),-1))), 12)
AND dt <= AddMonths('MONTH',-6,MonthEnd(AddMonths(MONTH,-1,Date(Now())))), custField))

after I debug one by one, I confirmed that this line cause the error:

dt <= AddMonths('MONTH',-6,MonthEnd(AddMonths(MONTH,-1,Date(Now()))))  has error. How should I fix it?

Essentially, I have this code in sql: DATEADD('MONTH',-6,LAST_DAY(DATEADD(MONTH,-1,CURRENT_DATE)))

and I use qlik logic to translate it as: AddMonths('MONTH',-6,MonthEnd(AddMonths(MONTH,-1,Date(Now()))))

Can anyone point me out what went wrong and how to fix above set expression? any thoughts?

[chrismarlow]

Hi,

I am not sure this is going to work, but think identifies your error that might move you forward. Your embedded 'AddMonths' is AddMonths(MONTH, vs AddMonths('MONTH', these both need to be the same, so;

AND dt <= AddMonths('MONTH',-6,MonthEnd(AddMonths('MONTH',-1,Date(Now())))) AND st_tx <> 'pending', custField))

as last line will stop you getting a syntax error.

An observation I needed to create a toy application with the following fields for this to not error;

type_cd, res_cd, le_id, cm_cd, yr, mnth, dt, st_tx, custField, YEAR_NO, MONTH

Cheers,

Chris.

[PrashantSangle]

Hi @alex_202 ,

If i debug this code "LAST_DAY(DATEADD(MONTH,-1,CURRENT_DATE))"

I understood you are trying to find Last day of previous month

and then you are finding out last 6th month end date.

If my understanding is correct then you can use any of the following solution

1: AddMonths(MonthEnd(Today(),-1),-6)

or

2: MonthEnd(Today(),-7)

Regards,

Prashant Sangle