Average Expression ?

Hi,
I want to calculate the Average Sales for given columns, eg
Jan Fab Mar April AVERAGE
100 100 100 100 100
20 20 10 15.66
30 10 50 30
Is it possible using an Expression ?
ThanksCreate a matrix report and change the aggregate from Sum to avg
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Vishal" wrote:
> Hi,
> I want to calculate the Average Sales for given columns, eg
> Jan Fab Mar April AVERAGE
> 100 100 100 100 100
> 20 20 10 15.66
> 30 10 50 30
> Is it possible using an Expression ?
> Thanks
>
>

Avareges for period

Hi,

I am trying to get average for sales for last 30 days.

Aggregation function for [Measures].[Avg Sales] is AverageOfChildren

Next code returns correct result

SELECT { [Measures].[Avg Sales] } ON columns

FROM [Sales]

where ([Dim Date].[Year - Day].[Day].&[2/14/2007]&[2]:[Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

But this query is not flexible. Should be selected day and returned value for 30 days back.

For example, I am trying to use next code

select

([Dim Date].[Year - Day].[Day].CurrentMember.lead(29) : [Dim Date].[Year - Day].[Day].CurrentMember

, [Measures].[Avg Sales]) // average for 30 days back

ON columns

FROM [SAles]

where ([Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

But recieved error "The Tuple function expects a tuple expression for the argument. A tuple set expression was used."

What the query should be to resolve this problem?

Thanks for help.

Try creating a query calculated measure for this,like:

With

Member [Measures].[Trailing30Sales] as

Aggregate({[Dim Date].[Year - Day].Lag(29) : [Dim Date].[Year - Day].CurrentMember},

[Measures].[Avg Sales])

select

{[Measures].[Trailing30Sales]} // average for 30 days back

ON columns

FROM [Sales]

where ([Dim Date].[Year - Day].[Day].&[3/15/2007]&[3])

|||

Thank you, very much!

It is very simple