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. Sticky Text Concatenation Problem on Grouped Datasets

3. Puzzling SQL problem with nulls and dates

4. NETCARD PRO

5. Creating a calculated member using dimension members

6. doing backup from client machine???

7. MDX - calculate member on dimension

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

9. Check MDX Calculated Members sintax in a VB program

10. Check MDX Calculated Members sintax

11. Calculated member w/2 time dimensions