Calculations involving NULLs

I have a Calulated Member that is calculating an average.
[Measures].[Overall Satisfaction]/[Measures].[Count Of Survey]
The problem is that for some of the items there are no surveys, so there are
NULL values in the database for these.
When I view the cube as a pivot table in Excel I am getting #NUM! for any
dimentions that do not have actual numbers.
How can I change my calculation so it will just return NULL instead of the
dreaded #NUM! value that drives managers so crazy?
Thank you!
:-J
Jeremy
Use COALESCE or ISNULL function to change a NULL to a valid value or a
space.
Anith
|||REGARDS MY EXAMPLE
[Measures].[Average] AS ' IIF(ISNULL([Measures].[Count Of Survey]), NULL, [Measures].[Overall Satisfaction]/[Measures].[Count Of Survey]) '
You colud use ISNULL() with a tuple, for example ISNULL(([Time].[2004].CURRENTMEMBER, [Measures].[Overall Sastisfaction))
I hope it would help you
Agustin
|||Thank you for the help it got me going on the right track.
What I ended up with was:
IIF([Measures].[Count Of Survey]=0, NULL, [Measures].[Overall
Satisfaction]/[Measures].[Count Of Survey])
This seems to work perfectly!
Thanks agian!
-Jeremy
"Agustin Brau" <anonymous@.discussions.microsoft.com> wrote in message
news:759562E4-29AE-4697-AEE4-4290AF5A524D@.microsoft.com...
> REGARDS MY EXAMPLE
> --
> [Measures].[Average] AS ' IIF(ISNULL([Measures].[Count Of Survey]), NULL,
[Measures].[Overall Satisfaction]/[Measures].[Count Of Survey]) '
> --
> You colud use ISNULL() with a tuple, for example
ISNULL(([Time].[2004].CURRENTMEMBER, [Measures].[Overall Sastisfaction))
> I hope it would help you
> Agustin