Is there a better way to calculate the moving average in an MDX in iccube?

Question

I'm using the following calculated member to calculate the moving average for my visits for the last 30 days period; is there a shorter way to do this?

WITH 
  MEMBER [Measures].[Visits Moving Avg] AS 
      AVG(
        [TIME].[Time].Prevmember : [TIME].[Time].Prevmember.Prevmember.Prevmember....
        , [Measures].[VISITS] 
      ), SOLVE_ORDER = 0

score 3 · Accepted Answer · edited Nov 19 '13 at 16:08

Instead of using the serie of prevMember.prevMember... calls you can use the Lag MDX function function as following :

WITH 
  MEMBER [Measures].[Visits Moving Avg] AS 
      AVG(
        [TIME].[Time].prevMember : [TIME].[Time].prevMember.lag(30)
        , [Measures].[VISITS] 
      ), SOLVE_ORDER = 0

By the way, it looks like currentMember is missing in your query; you are currently computing the moving average for the defaultMember of the [Time] dimension. The query using the currentMember of the time dimension is as following :

WITH 
  MEMBER [Measures].[Visits Moving Avg] AS 
      AVG(
        [TIME].[Time].currentMemBer.prevMember
            : [TIME].[Time].currentMemBer.prevMember.lag(30)
        , [Measures].[VISITS] 
      ), SOLVE_ORDER = 0

Is there a better way to calculate the moving average in an MDX in iccube?

1 Answers1