MS SQL Server OLAP - average in month

Author

average in month

jengi

2005-09-22, 9:23 am

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?

Deepak Puri

2005-09-24, 3:23 am

This MDX query for the Foodmart Sales cube may help:
[color=darkred]
With Member [Measures].[Cumulative Average] as
'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[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

jengi

2005-09-24, 3:23 am

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.

Deepak Puri

2005-09-25, 8:23 pm

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.droptable.com ***

Darren Gosbell

2005-09-26, 3:23 am

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_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1127545186.570956.325150@z14g2000cwz.googlegroups.com>,
jengiolo@yahoo.it says...
> 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.
>
>

jengi

2005-09-26, 3:23 am

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.

Darren Gosbell

2005-09-26, 3:23 am

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

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

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_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1127719008.632860.12220@o13g2000cwo.googlegroups.com>,
jengiolo@yahoo.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.
>
>

Sponsored Links