MDX to Calculate Moving Avarage

  • 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, Day

    The 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

    (

    [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) :

    [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,

    [Measures].[<<Target Measure>>]

    )

    // 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...:crying:

    Thank you really for any Help!!:-):-):-)

  • Try

    Avg(

    {[Time].[Month].CurrentMember.Lag(1):[Time].[Month].CurrentMember},

    [Measures].[Period Demand])

    Mack

  • Thank you!! I will try it and inform you!!:-):-):-):-)

  • The same error using this. I really dont understand. 🙁 :doze:-->

    'Avg(

    {[Time].[Month].CurrentMember.Lag(1):[Time].[Month].CurrentMember},

    [Measures].[Period Demand])'

  • The syntax I provided is correct (have checked :-P)

    What MDX query are you running to get the error?

    Mack

  • 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...

  • 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 query

    The syntax looks correct so it will be a contextual problem eg you are using a different date than the one specified

    Mack

  • Ok,

    I will try it again. Thanks for your Help.

  • 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!!!!!

  • Hello everyone!

    I did that :

    I created two new Measures:

    Measure : CountFinalStockDays with AggregateFunction : Count

    Measure : SumFinalStockDays with AggregateFunction : Sum

    New 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!!:-):-):-):-)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply