locked
Divide By Zero Error RRS feed

  • Question

  • I have the following calculated measure in my cube:

    ([Measures].[Actual Line Total], [DIM ESTIMATE].[Function Category].[FEE]) / ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])

    I'm trying to include an IIF statement in order to avoid divide by zero errors, but when I do I get an error message. Here's what I came up with:

    IIF

    (([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])=0, NULL,

    ([Measures].[Actual Line Total], [DIM ESTIMATE].[Function Category].[FEE]) / ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])        

    Error 4 MdxScript(ESTIMATES) (14, 1) Parser: The syntax for 'FORMAT_STRING' is incorrect.  0 0 

    The measure is set up to format as a percentage. Not sure where the problem is...!

    Any insight would be appreciated!

    Thanks


    A. M. Robinson


    • Edited by ansonee Monday, February 20, 2012 9:03 PM change
    Monday, February 20, 2012 9:02 PM

Answers

  • Hi ansonee,

    Is the code you have given, complete? Because, when I tried to check it, there is a parenthses missing over there, please see indented code below -

    IIF ( ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])=0, NULL, ([Measures].[Actual Line Total], [DIM ESTIMATE].[Function Category].[FEE]) / ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE]) 

    I can't see closing ")" for IIF. Please add ")" and see if it works for you.

    Regards,
    Santosh


    http://microsoftbizintel.wordpress.com/


    • Edited by Santosh Kumar Joshi Tuesday, February 21, 2012 1:58 AM
    • Proposed as answer by Challen Fu Tuesday, February 21, 2012 6:24 AM
    • Marked as answer by ansonee Tuesday, February 21, 2012 10:49 PM
    Tuesday, February 21, 2012 1:56 AM

All replies

  • Hi ansonee,

    Is the code you have given, complete? Because, when I tried to check it, there is a parenthses missing over there, please see indented code below -

    IIF ( ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])=0, NULL, ([Measures].[Actual Line Total], [DIM ESTIMATE].[Function Category].[FEE]) / ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE]) 

    I can't see closing ")" for IIF. Please add ")" and see if it works for you.

    Regards,
    Santosh


    http://microsoftbizintel.wordpress.com/


    • Edited by Santosh Kumar Joshi Tuesday, February 21, 2012 1:58 AM
    • Proposed as answer by Challen Fu Tuesday, February 21, 2012 6:24 AM
    • Marked as answer by ansonee Tuesday, February 21, 2012 10:49 PM
    Tuesday, February 21, 2012 1:56 AM
  • That was the ticket...wow.  ;-)

    Thanks!!!


    A. M. Robinson

    Tuesday, February 21, 2012 4:39 PM