I was wondering whether anyone can help me with a problem i have with
the average. I have to count (additive) average in Year.
Example:


Year Month Val Avg
2001 1 10 10
2001 2 20 15 (10+20)/2
2001 3 30 20 (10+20+30)/3
2001 4 40 25 (10+20+30+40)/4
2002 1 20 24 (10+20+30+40+20)/5
2002 2 45 27.5 (10+20+30+40+20+45)/6
2002 3 5 24.3 (10+20+30+40+20+45+5)/7

2,3,4,5,6,7... is the number of filtered(selected by user) month


Can anyone help me?

This MDX query for the Foodmart Sales cube may help:

'Sum([Time].[Month].Members.Item(0):[Time].CurrentMember,
[Measures].[Store Sales])/
Sum([Time].[Month].Members.Item(0):[Time].CurrentMember,
[Measures].[Sales Count])', FORMAT_STRING = '0.00'
select {[Measures].[Store Sales], [Measures].[Sales Count],
[Measures].[Sales Average], [Measures].[Cumulative Average]} on columns,
Non Empty [Time].[Month].Members on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thank you very much,
but I have to write a OWC query, so for example I can't use "Non Empty"
or "Select" syntax.
To count the filtered months (for average) I try with
"Count([DATE].[Month].Members)" but this doesn't work, this return all
months. I try also with "Count(StrToSet("Axis(0)")" but this give be
strange random values.

How can I solve the problem?
Thanks,
J.

Not sure I understand the OWC issue, since you can set the MDX query
text for OWC - maybe someone else know more?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

You should be able to use the just calculated member definition from the
previous answer -

Sum([Time].[Month].Members.Item(0):[Time].CurrentMember,
[Measures].[Store Sales])/
Sum([Time].[Month].Members.Item(0):[Time].CurrentMember,
[Measures].[Sales Count])

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1127545186.570956.325150 (AT) z14g2000cwz (DOT) googlegroups.com>,
jengiolo (AT) yahoo (DOT) it says...

The calculated member:
"Sum([Time].[Month].Members.Item(0):[Time].CurrentMember,[Measures].[Store
Sales])"
looks ok for me, but it give me this error:
"not valid levels (not correspondents) in the function Range"
What's the problem?
Thanks all
j.

Probably has to do with the second part of the time range not specifying
a level.

You could try, the following, but it may not work if you do not have the
month level in your select statement.

[Time].[Month].Members.Item(0):[Time].[Month].CurrentMember

Or you could try something like the range below to try and get the range
for the current level.

[Time].currentmember.level.Members.Item(0):[Time].CurrentMember

I have not tested either of these they are just off the top of my head,
but hopefully you can see the logic I am following.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1127719008.632860.12220 (AT) o13g2000cwo (DOT) googlegroups.com>,
jengiolo (AT) yahoo (DOT) it says...