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
by