Hi everybody,

I hope there is an answer to my question deep in the MDX universe...
Let's say we have Transaction Date dimension (Year-Qtr-Month-Day)
among others and we have Measure for Transaction Value. Now we want to
build calculated member for Avg Value Per Day. If we use :
Avg(Descendants([Transaction Date].CurrentMember,[Transaction
Date].[Day]),[Measures].[Transaction Value])
we are getting summ of values divided by distinct number of days in
the current tuple. If there are days when there was NO transactions,
they are missed.
The same would happen if I build distinct count measure and divide sum
of values by that count.

Now question: is there a way to figure out number of leave descentants
for Transaction Date REGARDLESS of unexistence of measures, but taking
into account slicing across other dimensions?
In other words: I slice by September and Tuesdays and I want sum of
trans values to be divided by number of Tuesdays in that September. Any
thoughts?

Thanks in advance.

Michael




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

If you want to include empty cells in your average, you should change:

[Measures].[Transaction Value]

to

iif(isempty([Measures].[Transaction Value]),0,[Measures].[Transaction Value])

As to the slicing requirement, could you explain how do you slice by
September and Tuesdays, since you can only include one member from each
dimension in the slicer tuple ? Do you have multiple time dimensions?
HTH,
Brian
www.geocities.com/brianaltmann/olap.html


"Michael" wrote:

Thanks Brian,

Multiple time dimensions - yes, otherwise I don't know how you can slice
transactions for Saturdays in September between 4pm and 8pm.

As for your formula - WOW, it works! MDX is definitely too new for me
. I thought that AVG is calculating number of non-empty cell in the
first argument set and uses it as denominator. But here you are using
second argument to redeclare emptiness. Thanks a lot!

Michael



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!