Date range Average

Question

• Hi

I would like to create a member to calculate the average of the data selected in the users query.

eg
Date Jan Feb Mar Apr
Measure 2 4 6 8
Average 5 5 5 5

if date selection changes, so will the average

Date Jan Feb Mar Apr May
Measure 2 4 6 8 10
Average 6 6 6 6 6

So within the script I want to reference the first and last date in the selection. Is this possible?

I did solve this solution using 2 extra date dimensions (from and to), but ths is too much work for the users to deal with (yeah I know...),
Here is how I did that:

Create member
CurrentCube.[Limit Type].[Limit Type].[Ave mR]
AS AGGREGATE(LinkMember([Limit From Date].[Limit From Date].CurrentMember,[Date].[Fiscal Date]):
LinkMember([Limit To Date].[Limit To Date].CurrentMember,[Date].[Fiscal Date]),[Limit Type].[Limit Type].[mR])
/
COUNT(LinkMember([Limit From Date].[Limit From Date].CurrentMember,[Date].[Fiscal Date]):
LinkMember([Limit To Date].[Limit To Date].CurrentMember,[Date].[Fiscal Date]));

Regards
Jamie
Monday, July 13, 2009 2:10 PM

• Hi jamie

You can use the AVG() function rather than doing SUM()/COUNT() . and Yes you can use only one dimension ( I am using DATE dimension of adventure works). If you are getting this dynamically in some report you will have to dynamically create Set using STrToSET(). Have  alook at the query below

```WITH
SET  MyDateRange AS {[Date].[Fiscal].[Month].&[2002]&[7]:[Date].[Fiscal].[Month].&[2002]&[9]}
MEMBER [Measures].[Avg] AS AVG(([Measures].[Internet Sales Amount],MyDateRange)),FORMAT_STRING = 'Currency'

SELECT {[Measures].[Internet Sales Amount],[Measures].[Avg]} on 0
,{MyDateRange} on 1
Result

 Internet Sales Amount Avg Jul-02 \$500,365.16 \$465,611.21 Aug-02 \$546,001.47 \$465,611.21 Sep-02 \$350,466.99 \$465,611.21

let us know how you get along

Ashwani Roy

Thursday, July 16, 2009 10:14 AM

All replies

• Hi Jamie,

It sounds to me like maybe you should just change the Measure AggregateFunction to Average or AverageOfChildren - am I missing something?

Mike
Thursday, July 16, 2009 7:46 AM
• Hi jamie

You can use the AVG() function rather than doing SUM()/COUNT() . and Yes you can use only one dimension ( I am using DATE dimension of adventure works). If you are getting this dynamically in some report you will have to dynamically create Set using STrToSET(). Have  alook at the query below

```WITH
SET  MyDateRange AS {[Date].[Fiscal].[Month].&[2002]&[7]:[Date].[Fiscal].[Month].&[2002]&[9]}
MEMBER [Measures].[Avg] AS AVG(([Measures].[Internet Sales Amount],MyDateRange)),FORMAT_STRING = 'Currency'

SELECT {[Measures].[Internet Sales Amount],[Measures].[Avg]} on 0
,{MyDateRange} on 1