This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

is there any way where we can use 1 instead of 0 in CASE WHEN's ELSE. using 1 in ELSE gives Error:Invalid Expression

Not sure I'm following... you can use either. Can use 99999999 if you want. Care to share your SQL expression?

((CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')AND{transaction.trandate}&lt;={today}THEN{transaction.amount}ELSE 0 END-CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')-1 THEN{transaction.amount}ELSE 0 END)*100)/ NULLIF(CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')-1 THEN{transaction.amount}ELSE 0 END,0)

this formula is to calculate the growth rate

(CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')AND{transaction.trandate}&lt;={today}THEN{transaction.amount}ELSE 0 END-CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')-1 THEN{transaction.amount}ELSE 0 END)/CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')-1 THEN{transaction.amount}ELSE 0 END)*100

this formula gives possible division by zero

You'll either want to do a NULLIF 0 in the part under the divide by.

```(CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')AND{transaction.trandate}&lt;={today}THEN{transaction.amount}ELSE 0 END-CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')-1 THEN{transaction.amount}ELSE 0 END)/NULLIF(CASE WHEN TO_CHAR({transaction.trandate},'YYYY')=TO_CHAR({today},'YYYY')-1 THEN{transaction.amount}ELSE 0 END, 0))*100```