none
Nested IIF Statement RRS feed

  • Question

  • I have a need to perform essentially a "double check" for a zero or NULL condition in an MDX statement, and was looking for some guidance on nested IIF statements - also on the ISEMPTY function.

    Essentially the query is: [Actual Fees] / ([Amount Recognized] / [Estimated Fees]) - [Estimated Fees]

    In the SSAS cube, both Amount Recognized and Estimated Fees can either be zero or  NULL. As you can see, if either of those conditions applies, a "divide by zero" error will be thrown. If Actual Fees is zero, it's no issue - it will just evelauate to zero.

    My problem is how do I alter my MDX to handle the two possible conditions? If Estimated Fees is zero, then the expression Amount Recognized/Extimated Fees throws the error, which in turn will cause the whole calc to fail with can't divide by zero.

    If Amount Recognized is zero, again the expression evaluates to zero, which will then cause the entire expression to error out.

    Ultimately, my end goal is if the denominator is going to evaluate to zero, then I want the entire expression to just display zero.

    A screen shot of the results right now:

    And my MDX:

    ([Measures].[Actual Line Total]) / (([Measures].[Recognized Amount])/([Measures].[Estimate Line Total])) - ([Measures].[Estimate Line Total])
    Thanks in advance!!


    A. M. Robinson

    Tuesday, March 19, 2013 3:38 PM

Answers

  • Try this (you can use OR instead of nesting IIFs?): 

     IIF(
     IsEmpty([Measures].[Recognized Amount]) 
     OR 
     [Measures].[Recognized Amount]=0 
     OR 
     IsEmpty([Measures].[Estimate Line Total]) 
     OR 
     [Measures].[Estimate Line Total]=0, 
     0, 
     ([Measures].[Actual Line Total]) / 
     (
      ([Measures].[Recognized Amount])/
      ([Measures].[Estimate Line Total])
     
     ) - ([Measures].[Estimate Line Total])
     )


    Jegan - SQL Server BI Consultant (http://EasyBI.wordpress.com)


    • Edited by jeganinfo Tuesday, March 19, 2013 5:49 PM
    • Marked as answer by ansonee Wednesday, March 20, 2013 5:50 PM
    Tuesday, March 19, 2013 5:49 PM

All replies

  • Try this (you can use OR instead of nesting IIFs?): 

     IIF(
     IsEmpty([Measures].[Recognized Amount]) 
     OR 
     [Measures].[Recognized Amount]=0 
     OR 
     IsEmpty([Measures].[Estimate Line Total]) 
     OR 
     [Measures].[Estimate Line Total]=0, 
     0, 
     ([Measures].[Actual Line Total]) / 
     (
      ([Measures].[Recognized Amount])/
      ([Measures].[Estimate Line Total])
     
     ) - ([Measures].[Estimate Line Total])
     )


    Jegan - SQL Server BI Consultant (http://EasyBI.wordpress.com)


    • Edited by jeganinfo Tuesday, March 19, 2013 5:49 PM
    • Marked as answer by ansonee Wednesday, March 20, 2013 5:50 PM
    Tuesday, March 19, 2013 5:49 PM
  • iif([Measures].[Recognized Amount]+0=0
    		, 0
    		, iif((([Measures].[Recognized Amount])/([Measures].[Estimate Line Total]))+0=0
    				, 0
    				, (([Measures].[Actual Line Total]) / (([Measures].[Recognized Amount])/([Measures].[Estimate Line Total]))) - ([Measures].[Estimate Line Total])
    			)
    		)	

    Hi ansonee ,

    Hope the next code will help you :)


    Regards, David .

    Tuesday, March 19, 2013 5:53 PM
  • David / Jegan:

    Thank you! Both methods worked equally well...I am seeing some weird results though.

    As you can see, Compionent_ID 9619-4 ans 9619-2 are WAY off!! I have no idea why those numbers are as off as they are. This seems to be occuring sporadically throughout the cube.

    Any ideas what could be the underlying caude?


    A. M. Robinson

    Tuesday, March 19, 2013 6:09 PM
  • My guess is the Recognized Amount is showing as 0 but internally represented as a really small float number.

    Can you try rounding [Recognized Amount] to the nearest integer to see if this is the problem. 

     IIF(
     IsEmpty([Measures].[Recognized Amount]) 
     OR 
     ROUND([Measures].[Recognized Amount])=0 
     OR 
     IsEmpty([Measures].[Estimate Line Total]) 
     OR 
     [Measures].[Estimate Line Total]=0, 
     0, 
     ([Measures].[Actual Line Total]) / 
     (
       ROUND([Measures].[Recognized Amount])/
      ([Measures].[Estimate Line Total])
     
     ) - ([Measures].[Estimate Line Total])
     )


    Jegan - SQL Server BI Consultant (http://EasyBI.wordpress.com)

    Wednesday, March 20, 2013 7:36 AM
  • Brilliant!!!

    A thousand thank you's!! Everyone...!


    A. M. Robinson

    Wednesday, March 20, 2013 2:27 PM