Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
MDX Query to calculate Average rather than Sum

Answered MDX Query to calculate Average rather than Sum

  • Saturday, November 24, 2012 11:23 AM
     
     
    Sign In to Vote
    0

    I have a cube that maintains ratings data per TV channel for every 15-min slot in the day (4 slots in hour / total 96 slots in day)

    I have a RatingTime dimension which has two attributes

         TimeSlot ===> represents 96 slots (06:00, 06:15, 06,30, 06:45, 07:00,..., 05:45)

         Daypart ===> time periods in a 24-hour day (For example)

    Morning   (06:00-09:00)
    Daytime (09:00-16:00)
    Early Evening (16:00-20:00)
    Prime Time (20:00-23:00)
    Late Prime (23:00-01:00)
    Late Night (01:00-03:00)
    Early Morning (03:00-06:00)

    The following MDX query will give me total rating per daypart:

    SELECT [Measures].[Rating] ON COLUMNS,
    [RatingTime].[Daypart].[Daypart] ON ROWS
    FROM [MonthlyRatings]

    Query Result:

    Daypart Rating
    Morning (06:00-09:00) 6.7
    Daytime (09:00-16:00) 103.4
    Early Evening (16:00-20:00) 146.0
    Prime Time (20:00-23:00) 144.9
    Late Prime (23:00-01:00) 92.3
    Late Night (01:00-03:00) 35.3
    Early Morning (03:00-06:00) 8.9

    The above figures are actually the sum of all timeslots involved in each daypart (Please refer to images)

    I need to get average rating per daypart rather than sum of rating, and this can perhaps be achieved by using the timeslot attribute of the RatingTime Dimension. If I can divide the sum of rating by the count of timeslots in each daypart. I'm not really an expert in MDX, but something like this:

    SELECT ([Measures].[Rating] / COUNT [RatingTime].[TimeSlot].[TimeSlot])

     ON COLUMNS,
    [RatingTime].[Daypart].[Daypart] ON ROWS
    FROM [MonthlyRatings]

    I hope the above makes sense to you.

    Any help is appreciated

    Hani

     

All Replies

  • Saturday, November 24, 2012 7:21 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Avg(
       Descendants(
          [Manager].[Nom Complet].Currentmember
       ), 
       [Measures].[PA Salaire]
    )

    See below link :

    http://www.bigresource.com/MS_SQL-calculated-member-MDX-Average-problem-QNMDUg3O.html

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

     
  • Saturday, November 24, 2012 8:13 PM
     
     
    Sign In to Vote
    0

    Many thanks Ahsan for your reply.

    Below is my actual MDX query:

    SELECT NON EMPTY [Measures].[Rating] ON COLUMNS,
    NON EMPTY [Rating Time ORG].[Daypart].[Daypart] ON ROWS
    FROM [MonthlyRatings]

    Honestly speaking, I couldn't figure out how to apply your formula on my query due to my limited experience with MDX, so I would be so grateful if you could show me exactly how to apply it

    Thanks again,

    Hani

     
  • Friday, November 30, 2012 4:53 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Create the average as a Caclulated Member within the scope of the query

    WITH MEMBER Measures.[Avg Rating] AS
   Avg(
      Descendants(
            [Rating Time ORG].[Daypart].Currentmember
      ), 
      [Measures].[Rating]
   )
SELECT NON EMPTY [Measures].[Avg Rating] ON COLUMNS,
NON EMPTY [Rating Time ORG].[Daypart].[Daypart] ON ROWS
FROM [MonthlyRatings]