MDX:Handling division by zero errors

  • Article

WITH
MEMBER [Date].[Calendar Year].[2006VS2007] AS
[Date].[Calendar].[Calendar Year].&[2007] /
[Date].[Calendar].[Calendar Year].&[2006],
FORMAT_STRING = 'Percent'
SELECT
{ [Date].[Calendar Year].[Calendar Year].&[2006],
[Date].[Calendar Year].[Calendar Year].&[2007],
[Date].[Calendar Year].[2006VS2007]  } *
[Measures].[Internet Sales Amount] ON 0,

non empty {([Product].[Product Categories].children
)
}
ON 1
FROM
[Adventure Works]

image

 

If you notice where Value of Internet Sales is Null for Base Year in our case CY 2006 the result is 1.#INF

In our case for Accessories and Clothing we are getting result as 1.#INF because of simple reasons 1/0 is 1.#INF

1.#INF – is nothing but formatted value of infinity

Another way of checking result of ‘1/0’ is given below

with  member [Measures].x
as 1/0
select
[Measures].x on 0
from [Adventure Works]

image

As expected result if infinity.

We can workaround this behavior by adding IIF statement, where we are stating if denominator is Zero then show NULL else perform Division.

WITH
MEMBER [Date].[Calendar Year].[2006VS2007] AS
iif([Date].[Calendar Year].[Calendar Year].&[2006]=0,NULL,
[Date].[Calendar].[Calendar Year].&[2007] /
[Date].[Calendar].[Calendar Year].&[2006]),
FORMAT_STRING = 'Percent'
SELECT
{ [Date].[Calendar Year].[Calendar Year].&[2006],
[Date].[Calendar Year].[Calendar Year].&[2007],
[Date].[Calendar Year].[2006VS2007]  } *
[Measures].[Internet Sales Amount] ON 0,

non empty {([Product].[Product Categories].children
)
}
ON 1
FROM

image