## Creating an MDX Calculated Member as an Average over Time Dimension

### 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)

Quote:},

[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.

Sebastian Armas.

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,