none
Null Calculations

    Question

  • Hi Guys

    I have a cube which i added a calculation to today. eg A/B,  however i noticed when i report off the cube in excel i get #NUM! in the value field. I know this is down to value B being empty or null. Is there a easy way to overcome this issue?

     

    Thanks in advance

    Dave

     

    Thursday, October 14, 2010 2:53 PM

Answers

  • WITH MEMBER [Measures].[A] AS 1

    MEMBER [Measures].[B] AS 0

    MEMBER [Measures].[C] AS

          IIf( [Measures].[B] = 0, NULL, [Measures].[A]/[Measures].[B] )

    SELECT      {

                [Measures].[C]

          } ON COLUMNS

    FROM  [Adventure Works]

     

    Changing it to MEMBER [Measures].[B] AS NULL also returns an empty cell. No divide by zero error is returned.


    http://martinmason.wordpress.com
    Sunday, October 17, 2010 6:24 PM

All replies

  • You could get more info about the error if, in the cube browser, you hover our mouse over the cell with the #NUM error; a tooltip should display with a description


    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, October 14, 2010 3:00 PM
  • Just states Value: #NUM! and row and column info...

    Thursday, October 14, 2010 3:02 PM
  • Could you try adding an IF statement to your calculation to check if the value is NULL then output some specifict error output?  for example

    with member MyMeasure as
    IIF([Measures].[Amount] = Null, "N/A", [Measures].[Amount])
    



    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, October 14, 2010 3:11 PM
  • Returning N/A may still give you the error as it is non numeric. You can use the NON_EMPTY_BEHAVIOR for calculation and filter out the NULL VALUES
    vinu
    Thursday, October 14, 2010 5:15 PM
  • Hi Vinuthan,

    Do you have a example of how to do this? You are correct in saying N/A gives a error.

    Dave

    Friday, October 15, 2010 7:37 AM
  • You can create your Calculated member like;

    CREATE MEMBER CURRENTCUBE.[Measures].[x] AS 
        IIF
        (
          B = 0 ,NULL,A/B
        ) 
       ,VISIBLE = 1;

     

    If you use B=0 then that will check for IsEmpty as well as NULL.

    Example using AdventureWorks;

    CREATE MEMBER CURRENTCUBE.[Measures].[x] AS 
        IIF
        (
          [Measures].[Internet Sales Amount] = 0
         ,NULL
         ,
            [Measures].[Internet Standard Product Cost]
          / 
            [Measures].[Internet Sales Amount]
        ) 
       ,VISIBLE = 1;


    Ani
    Friday, October 15, 2010 8:37 AM
  • Thanks Ani,

    I will check to see if this works later today. I remember doing something like this and it never worked but i will give this a go.

    Thanks again, will be in touch

    Dave

    Friday, October 15, 2010 9:27 AM
  • You need to check for both empty and 0.

    remove the iif condn and set the value of b for 0 and null and will see the issue.

    with 
    member a as 1
    member b as 0 
    member c as
    	IIF(ISEMPTY(b) OR b=0,NULL,a/b)
    select 
    {c} on 0
    from [Adventure Works]

    vinu
    Saturday, October 16, 2010 12:30 PM
  • You need to check for both empty and 0.

    remove the iif condn and set the value of b for 0 and null and will see the issue.

    with 
    
    member a as 1
    
    member b as 0 
    
    member c as
    
    	IIF(ISEMPTY(b) OR b=0,NULL,a/b)
    
    select 
    
    {c} on 0
    
    from [Adventure Works]

    vinu

    That's not correct. In MDX, there is no need to check for both empty and 0. As Ani stated in an earlier post, B=0 checks for both 0 and empty. Sometimes wished SQL would work the same way.

    HTH, Martin


    http://martinmason.wordpress.com
    Saturday, October 16, 2010 2:14 PM
  • hi martin,

    I believed the same but thought of trying some simple math with MDX and landed up checking the behavior for the query that i have mentioned above.  

    What i did was created a member b and set its value to 0 so it gives me divide by 0 error. Then have the expression as mentioned by you guys. so it returns null. Now set the value of b to null. and now it gives divide by zero error. So then i ended up having the IsEmpty() check. Now it worked properly for both usecases.

    let me know

     

    vinu
    Saturday, October 16, 2010 11:51 PM
  • WITH MEMBER [Measures].[A] AS 1

    MEMBER [Measures].[B] AS 0

    MEMBER [Measures].[C] AS

          IIf( [Measures].[B] = 0, NULL, [Measures].[A]/[Measures].[B] )

    SELECT      {

                [Measures].[C]

          } ON COLUMNS

    FROM  [Adventure Works]

     

    Changing it to MEMBER [Measures].[B] AS NULL also returns an empty cell. No divide by zero error is returned.


    http://martinmason.wordpress.com
    Sunday, October 17, 2010 6:24 PM
  • You are right. I messed up such a simple thing
    vinu
    Monday, October 18, 2010 2:29 AM