MDX query help !

Hi,

I have the following query :

WITH
// get "5 period average sales"
MEMBER [Measures].[Sales_5Pavg] AS
'AVG({[Timeofday].[Dt].CURRENTMEMBER.LAG(3):[Timeofday].[Dt].CURRENTMEMBER},[Measures].[Sales])',FORMAT_STRING="#,##0.000"
// get the % which "Latest Sales > (Average of 5 periods Sales) "
MEMBER [Measures].[%up] AS
'([Measures].[Sales]-[Measures].[Sales_5Pavg])/[Measures].[C_5Pavg]',FORMAT_STRING="PERCENT"

SELECT {[Measures].[Sales],[Measures].[Sales_5Pavg],[Measures].[%up]} on 0,
ORDER (
FILTER (
[Product].[Product].CHILDREN,
([Measures].[Sales] > [Measures].[Sales_5Pavg])
),
[%up],
BDESC
) on 1
from SalesCube
where ([Timeofday].[Dt].&[2006-09-29T00:00:00]);

Sales Sales_5Pavg %up
productA 100 150 50%
productB 80 60 33.33%
productC 1200 1000 20%
.......etc

Above query get those products which sales greater than normal (i.e. 5 period average sales)
and sort the "greater than normal" percentage in decending order.

Since the query's period is date. May I know how to make the query more generic ?
e.g. adjust query so that it would be able to calculate base on month / quarter / year period ?

I have [Timeofday] dimension with the following hierarchy
[yr_dt] --> [Yr] --> [Hyr] --> [Qtr] --> [Mon] --> [Dt]

Please kindly advice and help. Thanks a lot !!

Hello. I am not sure from your MDX if you are using Analysis Services 2000 or 2005.

Here is a link to more information regarding Analysis Services 2005

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

Here is a link to how you can solve this in Analysis Services 2000:

http://www.tomchester.net/

HTH

Thomas Ivarsson