How do i avoid the divide by 0 error NetSuite Professionals #suiteanalytics

Join Slack

How do i avoid the divide by 0 error?

# suiteanalytics

Joseph Lee

12/08/2022, 5:54 PM

How do i avoid the divide by 0 error?

George McMullen

12/08/2022, 7:31 PM

You could see if NULLIF() works for your use case.

Joseph Lee

12/12/2022, 5:29 PM

i tired it couldnt figure out where exactly to add nullif. i kept getting invalid expression

George McMullen

12/12/2022, 7:47 PM

Could try to wrap the entire denominator under the

with NULLIF:

Copy code

(
  CASE
    WHEN TO_CHAR({trandate},'MM/YY')=TO_CHAR({today},'MM/YY')
    THEN {amount} 
    ELSE 0
  END
)
/
NULLIF(
        ( 
          ( 
            CASE
              WHEN TO_CHAR({trandate},'MM/YY')=TO_CHAR(TO_DATE(ADD_MONTHS({today},-1)),'MM/YY')
              THEN {amount}
              ELSE 0
            END
          )
          +
          ( 
            CASE
              WHEN TO_CHAR({trandate},'MM/YY')=TO_CHAR(TO_DATE(ADD_MONTHS({today},-2)),'MM/YY')
              THEN {amount}
              ELSE 0
            END
          )
          +
          (
            CASE
              WHEN TO_CHAR({trandate},'MM/YY')=TO_CHAR(TO_DATE(ADD_MONTHS({today},-3)),'MM/YY')
              THEN {amount}
              ELSE 0
            END
          )
        )
        /3
,0)

George McMullen

12/12/2022, 7:50 PM

Could also try changing the

ELSE 0

in the

CASE

statements in the denominator under the

ELSE NULL

but I’m not exactly sure if that will allow for all the addition. I can’t remember if

NULL + 5

would equal 5 or NULL. 🤣

Shawn Talbert

12/19/2022, 6:14 PM

this makes me want to cry

4 Views

Open in Slack

Previous Next