1. Creating an MDX Calculated Member as an Average over Time Dimension

Hello:

I'm currently working on a retail cube solution, and I'm stuck with the

Average Stock indicator.

The indicator works thus:

a) The following applies for the Store N1, Product N 1,

for the first three days of a given week:

On Monday, Stock = 4

On Tuesday, Stock = 6

On Wednesday, Stock = 8

For the three days, the Average Stock for Store N1, Product N 1,

= 6 = (4+6+8) / Distinct Number of Days = 18 / 3

b) Now, the following applies for the Store N2, Product N 1,

for the first three days of a given week:

On Monday, Stock = 6

On Tuesday, Stock = 8

On Wednesday, Stock = 13

For the three days, the Average Stock for Store N2, Product N 1,

= 9 = (6+8+13) / Distinct Number of Days = 27 / 3

c) Finallly, considering both Store N1 and Store N2, for Product N 1,

On Monday, Stock = 10

On Tuesday, Stock = 14

On Wednesday, Stock = 21

So, for the three days, the Average for Store N1 and Store N2, Product

N 1,

= 15 = (10+14+21) / Distinct Number of Days = 45 / 3

As you can see, this is a rather complex indicator, that for any given

period (a specific day, week, month, etc) is the SUM() of its individual

members values, but across a number of days, you have to divide by the

disticnt

number of days.

In fact, the formula

[Average Stock] = Iif([Time].CurrentMember.Children.Count = 0 ,

[Measures].[Stock],

[Measures].[Stock]/[Measures].[Distinct Days])

does gives the required results, having the primitive Measures defined as:

[Stock]= SUM("dbo"."FT"."Stock")

[Distinct Days] = Distinct(Count("dbo"."FT"."Date"))

The problem with this approach is that if you try to display this data

on EXCEL (with Pivot Table Service) and you select a subset of values of

the

[Time] dimension (like Monday and Wednesday, excluding Tuesday), the

[Distinct Days] indicator gets undefined and so does the [Average Stock]

calulated Member.

On the other hand, you can try the following formula:

[Average Stock] = AVG(

{

Descendants([Time].CurrentMember,

[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,

LEAVES)

[Measures].[Stock]

)

It works exactly as the "Distinct Days" approach, but if you select a

subset of values

of the [Time] dimension (like Monday and Wednesday, excluding Tuesday), the

[Average Stock] member isn't undefined, but it doesn't reflect de selected

days data;

it reflects the data of the three days, ignoring the days excluded.

Both approaches behave exactly the same, wether you select a subset of

days, weeks,

months, years or whatever hierarchy levels you define for the [Time]

dimension.

One goes undefined, the other ignores your selection.

If anyone has any clue whatsoever at some way to solve this problem,

please e-mail

me back through the newsgroup or through my e-mail account.

Thanks in advance,

Sebastian Armas.

2. Opinions on Codebase?

3. thegolfspot.com

4. The Marketing Warehouse

5. MDX-syntax to check which time dimension to use in a calculated member

6. Error -952 on console screen of Informix SE

7. Urgetn MDX question - Calculated measure depends on another dimension members

8. Major MDX problems with Calculated member (this is a good test for MDX)

9. MDX - Member properties in a calculated member

10. Analysis Services, Dimension Member ordering, Calculated Members

11. Adding Calculated Member to a Parent Member in the dimension through code