Forums › Cached › Customization › Searches / List Views

This topic contains 1 reply, has 0 voices, and was last updated by Olivier Gagnon NC 6 years, 9 months ago.

  • Author
    Posts
  • January 26, 2018 at 8:45 am #22607 Score: 0

    George Williams
    • Contributions: 0
    • Level 1
    • ☆

    I am trying to create a growth rate search using a formula (present-past)/past. i was successful in using a decode function to get the result for past and present, but when i try to preform the final calculation i am getting a divide by zero error. here is the formula

    (CASE TO_CHAR({trandate},'YYYY') WHEN '2017' THEN decode(to_char({trandate},'YYYY'),2017,{quantity}, 0) ELSE 0 end – CASE TO_CHAR({trandate},'YYYY') WHEN '2018' THEN decode(to_char({trandate},'YYYY'),2018,{quantity}, 0) ELSE 0 END)/CASE TO_CHAR({trandate},'YYYY') WHEN '2018' THEN decode(to_char({trandate},'YYYY'),2018,{quantity}, 0) ELSE 0 END

    i am able to get results for the subtraction part, only get the error when i try to divide

    Any help is appreciated.
    This is a cached copy. Click here to see the original post.

  • January 26, 2018 at 1:52 pm #22608 Score: 0

    Olivier Gagnon NC
    • Contributions: 0
    • Level 1
    • ☆

    wrap your last case statement in: nullif(CASE TO_CHAR({trandate},'YYYY') WHEN '2018' THEN decode(to_char({trandate},'YYYY'),2018,{quantity}, 0) ELSE 0 END,0)

  • Author
    Posts

You must be logged in to reply to this topic.