MDX to Calculate Moving Avarage

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » MDX to Calculate Moving Avarage

MDX to Calculate Moving Avarage

Rate Topic

Display Mode

Topic Options

Author

Message

rena24

Posted Monday, October 22, 2012 1:39 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

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

Post #1375704

Mackers

Posted Tuesday, October 23, 2012 1:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:58 AM
Points: 117, Visits: 458

Try

Avg(
{[Time].[Month].CurrentMember.Lag(1):[Time].[Month].CurrentMember},
[Measures].[Period Demand])

Mack

Post #1375871

rena24

Posted Tuesday, October 23, 2012 1:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

Thank you!! I will try it and inform you!! Smile

Post #1375880

rena24

Posted Tuesday, October 23, 2012 2:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

The same error using this. I really dont understand. Sad

-->

'Avg(
{[Time].[Month].CurrentMember.Lag(1):[Time].[Month].CurrentMember},
[Measures].[Period Demand])'

Post #1375887

Mackers

Posted Tuesday, October 23, 2012 2:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:58 AM
Points: 117, Visits: 458

The syntax I provided is correct (have checked Tongue

)

What MDX query are you running to get the error?

Mack

Post #1375892

rena24

Posted Tuesday, October 23, 2012 3:45 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

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

Post #1375936

Mackers

Posted Tuesday, October 23, 2012 3:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:58 AM
Points: 117, Visits: 458

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

Post #1375939

rena24

Posted Tuesday, October 23, 2012 6:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

Ok,

I will try it again. Thanks for your Help.

Post #1375998

rena24

Posted Wednesday, October 24, 2012 10:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

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

Post #1376581

rena24

Posted Wednesday, October 24, 2012 1:17 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

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

Post #1376640

« Prev Topic | Next Topic »

Permissions