Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    78

    Unanswered: If statment and calculation

    [Measures].[Net]/([Measures].[Spot Length]/30)

    This is my current expression, I am wondering how can I use the IF statement to say that if the denominator is 0, then set the value to null. (Right now I get error since you can't divide by 0). If the denominator is not 0, do the regular calculation.

    Please help. Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    CASE WHEN Measures.[Spot Length] / 30 <> 0
         THEN Measures.Net / ( Measures.[Spot Length] / 30 )
         END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    78
    Thank you so much. I figure it out too. The code is only a bit different. Hope it works, right now waiting for it to deploy

    Case when [Measures].[Spot Length]/30 = 0 THEN 0
    Else [Measures].[Net]/([Measures].[Spot Length]/30)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mangolili View Post
    ... THEN 0
    this is ~not~ what you said you wanted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    78
    If the denominator is 0, then I want the value to be null or 0, sorry I didn't specify it before. Either way works for me. I used it in my cube, but got some syntax error. I guess I need to somehow convert it to mdx.

  6. #6
    Join Date
    Jul 2011
    Posts
    78
    I figure out my careless mistake, I forgot to put in an end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •