## MDX to Calculate Moving Avarage

 Author Message rena24 SSC Rookie Group: General Forum Members Points: 40 Visits: 316 Hello everyone,I am a mdx newbie and I neep (please) your Help. I have created a Cube that has 3 dimensions. Products, Time and Warehouses. The Time Dimension is :Time --> Year , Quarter, Month, Week, DayThe Measure tha I need for my Calculation is [Store Demand]I have searched to find the formula for the Moving Average for each one Month and I found this :`///*Returns the average value of a member over a specified time interval.*/CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average]AS Avg([<>].[<>].CurrentMember.Lag(<> :[<>].[<>].CurrentMember,[Measures].[<>])// This calculation returns the average value of a member over the specified time interval.)'`So I wrote my MDX --> `'Avg( [Time].[Month].CurrentMember.Lag(1):[Time].[Month], [Measures].[Period Demand]'`But I get the error...#VALUE! The CURRENTMEMBER function expects a hierarchy expression for the 0 argument...Sorry, I really dont understand it...Thank you really for any Help!!:-):-):-) Mackers SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 499 TryAvg( {[Time].[Month].CurrentMember.Lag(1):[Time].[Month].CurrentMember}, [Measures].[Period Demand])Mack rena24 SSC Rookie Group: General Forum Members Points: 40 Visits: 316 Thank you!! I will try it and inform you!!:-):-):-):-) rena24 SSC Rookie Group: General Forum Members Points: 40 Visits: 316 The same error using this. I really dont understand. --> `'Avg({[Time].[Month].CurrentMember.Lag(1):[Time].[Month].CurrentMember},[Measures].[Period Demand])'` Mackers SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 499 The syntax I provided is correct (have checked :-P)What MDX query are you running to get the error?Mack rena24 SSC Rookie Group: General Forum Members Points: 40 Visits: 316 Thank you again. I dont know if I am wrong but I use it on the cube. I create the Calculate Member with this syntax... Mackers SSC-Enthusiastic Group: General Forum Members Points: 117 Visits: 499 What is the syntax of the query you are running (not the calculated member)? If you are using Excel you need to run a trace on the server to get the MDX queryThe syntax looks correct so it will be a contextual problem eg you are using a different date than the one specifiedMack rena24 SSC Rookie Group: General Forum Members Points: 40 Visits: 316 Ok,I will try it again. Thanks for your Help. rena24 SSC Rookie Group: General Forum Members Points: 40 Visits: 316 Hello again...I have a different problem now... I could not calculate the moving avarage with an MDX Query so I tried it with sql. It works but I realy know that the correct way would be an mdx query... Anyway, so I have the moving Avarage on the Day basis but I want to have an avg in every Time level... So I tried this code -->`'iif ([Time].currentmember.level.Name="(All)",Avg({DESCENDANTS([Time].CURRENTMEMBER,4)},[Measures].[MovingAvarage]),iif ([Time].currentmember.level.Name="Year",Avg({DESCENDANTS([Time].CURRENTMEMBER,3)},[Measures].[MovingAvarage]),iif ([Time].currentmember.level.Name="Quarter",Avg({DESCENDANTS([Time].CURRENTMEMBER,2)},[Measures].[MovingAvarage]),iif ([Time].currentmember.level.Name="Month",Avg({DESCENDANTS([Time].CURRENTMEMBER,1)},[Measures].[MovingAvarage]),iif ([Time].currentmember.level.Name="Day",[Measures].[MovingAvarage],NULL)))))'`But it seems tha it Sums the Data In each Time level...I get the MovingAvarage from my View and his Aggregate property is to sum...I think there is the problem... I use SQL Server Standard Edition and I cant select for the Aggregate property the none property.I run my MDX on the cube and I check the results on the Cube Browser. The MDX is so different from SQL and I really cant run anything on the SQL Server Analysis Query Analysizer..... What am I doing wrong? Is there any other way? I really dont know and I need really your Help... Please Help me!!!!! rena24 SSC Rookie Group: General Forum Members Points: 40 Visits: 316 Hello everyone!I did that : I created two new Measures:Measure : CountFinalStockDays with AggregateFunction : CountMeasure : SumFinalStockDays with AggregateFunction : SumNew Calculate Measure : [FinalStockDays] --> MDX :'[Measures].[SumFinalStockDays]/[Measures].[CountFinalStockDays]'And it works now. Thank you all for you help! And if there is a better way to do this please tell me!!:-):-):-):-)