none
Avg Count by hour, by day of the week (MDX From ***)

    Question

  • Hello MDX Gurus,

     

    I'm going to do my best to explain what I'm trying to do, so please hang with me?

     

    I have a date dimension with 2 hierarchies...Calendar is one hierarchy that's structred like so Year->QTR->Month->Day (nothing grand about that).

    I have another hierarchy in the same dimension called Day Name with one level that represents day name of the week.

     

    Lastly, there is another dimension named Dim_Hours that has 1 hierarchy with 1level that represents the 24 hours of the day.

    I have 1 measure called hotel occupancy.

     

    First I needed to get the average of hotel occupancy by hour, by day and I accomplished this with the MDX below:

     

    CREATE MEMBER CURRENTCUBE.[MEASURES].AVG_OCC_CT
     AS AVG(
        DESCENDANTS([DIM DATE_H].[CALENDAR],[DIM DATE_H].[CALENDAR].[DATE]),
    [Measures].[OCC CT]),
    VISIBLE = 1

     

    This was needed because otherwise it would've summed every hour of the day and that would've been inaccurate

     

    Here's my issue...when I have the hours on the row, Calendar hierarchy on the filter and the Day Name hierarchy on the column, it looks like this:

     

    Hours,    Sun,Mon,Tue,Wed,Thur,Sat,Grand Total

    12:00AM, 222, 222, 222, 222, 222, 222, 222, 222
    1:00AM,   222, 222, 222, 222, 222, 222, 222, 222
    2:00AM,    223, 223, 223, 223, 223, 223, 223, 223
    3:00AM,   223, 223, 223, 223, 223, 223, 223, 223
    4:00AM, 223, 223, 223, 223, 223, 223, 223, 223

     

    As you might imagine, I was hoping to get the average of all Sundays,Mondays, etc but it's repeating values.

     

    Can anyone help with this?

     

    Thanks,

     

    Friday, August 08, 2008 4:03 PM

All replies

  • I think you need to put the Day of week in the Calc member. Something like the following:

     

     

    CREATE MEMBER CURRENTCUBE.[MEASURES].AVG_OCC_CT
     AS AVG(([DIM DATE_H].[Day of Week].currentmember*
        DESCENDANTS([DIM DATE_H].[CALENDAR],[DIM DATE_H].[CALENDAR].[DATE])),
    [Measures].[OCC CT]),
    VISIBLE = 1

     

    I have had to do this as well to support day of week analysis for calculated members since the Day of Week is not part of the hierarchy in the original calc member def...

    Friday, August 08, 2008 4:13 PM