I am going crazy trying to work with a cube that has a lot of NULL values.
I'm trying to average numbers and also create rolling 3 mouth averages. The
problem is that I can't figure out the avg() function. I thought it would do
as Excel does and ignore the NULL values. How do I get this kind of
calculation using MDX for a calculated measure?

Thanks for the help!!!

We're using AS 2000. Thanks Again.

"Scott Mescall" wrote:

you have to exclude these nulls from the formula using a filter like:
avg(filter(<3monthsmembers>, not isempty(measures.sales))
, measures.sales)


"Scott Mescall" <ScottMescall (AT) discussions (DOT) microsoft.com> wrote

But keep in mind that the Avg() function already excludes empty cells by
default:

http://msdn2.microsoft.com/en-us/lib...7(SQL.90).aspx

Returns the average value of measures or the average value of an
optional numeric expression, evaluated over a specified set.
...
The Avg function calculates the average of the nonempty values of cells
represented by Set_Expression by first calculating the sum of values
across cells in the specified set, and then dividing the calculated sum
by the count of nonempty cells in the specified set.
...

With AS 2000, one problem could be the interpretation of null values in
fact row measure fields as zeros:

http://support.microsoft.com/default...b;en-us;244650
...
Question: How does OLAP Services read NULL measure data in a fact table?

Answer: If there is no record in the fact table for a certain
combination of dimension members, (such as [1997].[Quarter1].[January],
[USA].[Texas].[Dallas] ) then, on cube processing, OLAP Services treats
the corresponding cell in the cube as empty. However, when there are
records in the fact table that have one or more null measure columns,
OLAP Services assigns a 0 value to the corresponding cells in the cube.

This behavior may not always be desirable, particularly, when the user
has to make a distinction in the cube's measure between a fact table
NULL and zero values. If such a distinction must be made, then the user
has to make sure that the fact table (or tables) do not have records
with empty measure columns. One solution is to break these empty measure
columns into different fact tables. The steps necessary to implement
this solution are:

1. Create multiple fact tables. One for each measure that needs a
distinction to be made between a NULL and a zero value.

2. Create a cube based on each fact table.

3. Create a virtual cube that includes all the base cubes to view all
the measures together.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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