locked
Calculate Average in MDX RRS feed

  • Question

  • Hi All,

    I want to calculate Average of Net Revenue for Last 4 Mondays . Example , if we run the report on Wednesday then we should get Average of last 4 Wednesday's.

      I came up with the following MDX expression :

    Avg

     

    (

    {[Date].[Fiscal Date].

    currentmember,[Date].[Fiscal Date].currentmember.lag(7),[Date].[Fiscal Date].currentmember.lag(14),[Date].[Fiscal Date].currentmember.lag(21)}

     

    ,

    [Measures].[Net Revenue])

     

    Any Suggestions for a better solution for this ?

    Wednesday, June 30, 2010 10:56 PM

Answers

  • Hi Ngulati,

    Here is some mdx to get the avg of the last 4 Mondays.  You might need to adapt it, if you want the "preceding 4 mondays".  Also, I assume that you have a hierarchy called [Day of Week], which contains the week day.

    Avg

     

    (tail(nonempty(

    {[Date].[Fiscal Date].[Fiscal Day]

    *[Date].[Day Of Week].[Monday],[Measures].[Net Revenue]),4),

    ,[Measures].[Net Revenue])

     


    Richard
    Thursday, July 1, 2010 1:44 AM
  • No problem,

    Just need to put the currentmember as the end of the set.   Try this

    Avg

     

    (tail(nonempty(null:[Date].[Fiscal Date].currentmember

    *[Date].[Day Of Week].[Monday],[Measures].[Net Revenue]),4)

    ,[Measures].[Net Revenue])


    Richard
    Thursday, July 1, 2010 9:05 PM

All replies

  • Hi Ngulati,

    Here is some mdx to get the avg of the last 4 Mondays.  You might need to adapt it, if you want the "preceding 4 mondays".  Also, I assume that you have a hierarchy called [Day of Week], which contains the week day.

    Avg

     

    (tail(nonempty(

    {[Date].[Fiscal Date].[Fiscal Day]

    *[Date].[Day Of Week].[Monday],[Measures].[Net Revenue]),4),

    ,[Measures].[Net Revenue])

     


    Richard
    Thursday, July 1, 2010 1:44 AM
  • seems there is a syntax issue with that MDX Richard. Also, where does the "4" come into play?
    FJK
    Thursday, July 1, 2010 5:33 PM
  • Ok, after some thought and research I figured out what you are doing with that calc and where the syntax error was.


    FJK
    Thursday, July 1, 2010 6:19 PM
  • Thanks Richard for the Reply !

    It works fine for the Current Date when you run the query.But I am looking for a solutions where I could run the report at any point in time to look for last 4 Mondays. Example , if the user select this calculated measure [Measures].[Avg 4 Week] on any day in the past , the measure should return 4 weeks average from that point of time. 

     

    Thanks,

    Ngulati

    Thursday, July 1, 2010 6:30 PM
  • No problem,

    Just need to put the currentmember as the end of the set.   Try this

    Avg

     

    (tail(nonempty(null:[Date].[Fiscal Date].currentmember

    *[Date].[Day Of Week].[Monday],[Measures].[Net Revenue]),4)

    ,[Measures].[Net Revenue])


    Richard
    Thursday, July 1, 2010 9:05 PM