All,

I'm trying to create a work around for how OLAP/AS 2000 replaces NULL values
with 0's. I have a sparely populated cube and I need to create an average
and 3 month rolling average calculated members. The problem is that
replacing NULL with 0 creates a wrong answer using the MDX Avg() function.

The only solution I could dream up for the NULL/0's issue was to create a
"NULL flag measure". This measure is either 0 or 1 depending if the actual
measure I'm using has a NULL or a hard value in the record. This is handled
during the ETL processing.

The 3 month rolling average calculation is something I'm having trouble
with. I'm trying to use the iif() function and I've gotten this far:

iif(([Measures].[Float Satisfaction Measure YN])>=1, (LastPeriods (3,
[Time].[Month].Dimension.CurrentMember)), ([Measures].[Float Satisfaction] /
[Measures].[Float Satisfaction Measure YN]), NULL)

The problem is that the part in the iif() function containing the
LastPeriods() function is looking for an expression and not a set/tuple. If
I wrap the calcuation in an Avg() function, I get an Average of an Average,
which gives me the wrong answer.

How do I get the (LastPeriods (3, [Time].[Month].Dimension.CurrentMember)),
([Measures].[Float Satisfaction] / [Measures].[Float Satisfaction Measure
YN]) portion of the iif statetment to be accepted as an expression by the
compiler.

Thanks for any help you can provide.

-Scott Mescall

Hi Scott,

The most efficient solution in your scenario might be to create a
separate cube for each measure which has NULL values in the fact table,
and then filter out the NULL fact records for each cube, either by
defining a Source Table Filter or using SQL views. But if I understood
your current question, separate sums across the numerator and
denominator might work:

Sum(LastPeriods(3, [Time].CurrentMember),
[Measures].[Float Satisfaction]) /
Sum(LastPeriods(3, [Time].CurrentMember),
[Measures].[Float Satisfaction Measure YN]), NULL)

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

The seperate cube idea seemed a little much for 25 metrics. This cube is
nothing but averages!?! Thank you for looking at the MDX statement. It
seems to be working like a champ.

Sometimes you just get too close to a problem to see the solution...

Thanks!!!

"Deepak Puri" wrote: