by 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]Quote:},
)
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.