# 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?

Dave

Thursday, October 14, 2010 2:53 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

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.

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

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

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