Hi ,
as I understand, the concept of AverageOfChildren aggregate function
applies, when aggregation is done via the time dimension. Is there a elegant
way to handle missing values as 0?

let's say, we have the following fact table,
TimeDim: Measure ACT
Mo: 2
Tu: -- missing (no entry in Fact Table)
We: 1
Th: -- missing
Fr:-- missing
Sa: 3
So: 8

The AverageOfChildreen function will return a week-value of 3.5 = (2+1+3+8)
/ 4

What I would like to have is a value of 2 = (2+0+1+0+0+3+8)/7 (days in a
week as setup in the time dimension)

Obviously we can solve this issue by creating a calculated measure based on
the ACT (Using SUM as aggregate function)
IIF(

( ISLEAF([Day].[~ ISO Date].CURRENTMEMBER)

),

IIF(ISEMPTY([Measures].[ACT]),0,[Measures].[ACT]),

IIF(ISEMPTY([Measures].[ACT]),0,[Measures].[ACT]) / [Measures].[Day Count]

)

Any better ideas, will be truly appreciated.

Thanks

Björn

Hi Björn,

How about adding a scoped calculation to the cube MDX script, rather
than a calculated measure - the ACT measure should still use
AverageOfChildren aggregate function:

Zero out time leaves of an AverageOfChildren measure:
*/

Scope([Measures].[ACT]);
Scope(Leaves([TimeDim]));
this = CoalesceEmpty([Measures].[ACT], 0);
End Scope;
End Scope;

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Works fine,
thank's Deepak

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> schrieb im Newsbeitrag
news:OHHbVfipGHA.4032 (AT) TK2MSFTNGP03 (DOT) phx.gbl...