none
Caculate average in MDX

    Question

  • Hello,

    I'm trying to caculate the average number of days between days dates, booking date et arrival date in MDX.

    I have the details, but I don't know how to calculate to average for the total period.

    Here my query :

    with set bookingPeriod as [Booking Date UTCP0100].[Date].[2013-10-25]:[Booking Date UTCP0100].[Date].[2013-10-31]

    member [Measures].[DayNumber] as ([Arrival Date].[Date].CurrentMember.MemberValue-[Booking Date UTCP0100].[Date].CurrentMember.MemberValue)

    member [Measures].[DayCount] as [Measures].[Bookings Count]*[Measures].[DayNumber]

    select {    

    [Measures].[Bookings Count],    

    [Measures].[DayNumber],    

    [Measures].[DayCount]}

    on 0,{    

    order(bookingPeriod, [Booking Date UTCP0100].[Date].CurrentMember.MemberValue, desc)

    } on 1

    from [Booking_Cube]

    where [Arrival Date].[Date].[2013-10-31]

    and the result :

                           Bookings Count    DayNumber    DayCount
    2013-10-31    522                      0                     0
    2013-10-30    469                      1                     469
    2013-10-29    383                      2                     766
    2013-10-28    356                      3                     1068
    2013-10-27    245                      4                     980
    2013-10-26    165                      5                     825
    2013-10-25    168                      6                     1008

    There are 522 bookings the 2013-10-31 to arrive 2013-10-31, 469 bookings the 2013-10-30 to arrive 2013-10-31, etc.

    The avg should be sum(daysCount) / sum(bookingCount)

    here 5116/2308 = 2.21

    How can I achieve this ?

    Thanks

    Antoine


    • Edited by AntoinePro Thursday, January 16, 2014 10:01 AM
    Thursday, January 16, 2014 9:59 AM

Answers

  • Hi Antoine,

    According to your description, you want to calculate the average value for the total period. In this case, we can use SUM function to get the total value of Bookings Count and Day Count. And then calculate the vaerage value. Here is the sample query.

    with set bookingPeriod as [Booking Date UTCP0100].[Date].[2013-10-25]:[Booking Date UTCP0100].[Date].[2013-10-31] 
    
    member [Measures].[DayNumber] as ([Arrival Date].[Date].CurrentMember.MemberValue-[Booking Date UTCP0100].[Date].CurrentMember.MemberValue) 
    
    member [Measures].[DayCount] as [Measures].[Bookings Count]*[Measures].[DayNumber] 
    
    member [Measures].[SumBookingCount] as sum([Booking Date UTCP0100].[Date].[2013-10-25]:[Booking Date UTCP0100].[Date].[2013-10-31],[Measures].[Bookings Count])
    
    member [Measures].[SumDayCount] as sum([Booking Date UTCP0100].[Date].[2013-10-25]:[Booking Date UTCP0100].[Date].[2013-10-31],[Measures].[DayCount])
    
    member [Measures].[AvgPired] as [Measures].[SumDayCount]/[Measures].[SumDayCount] 
    
    select [Measures].[AvgPired]
    
    on 0
    
    from [Booking_Cube] 
    
    where [Arrival Date].[Date].[2013-10-31]
    

    If I have anything misunderstood, please point it out.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Friday, January 17, 2014 8:40 AM
    Moderator
  • Hi Charlie,

    I change the calculated member

    member [Measures].[AvgPired] as [Measures].[SumDayCount]/[Measures].[SumDayCount] 

    by

    member [Measures].[AvgPired] as [Measures].[SumDayCount]/[Measures].[SumBookingCount] 

    and works great !


    Thanks a lot for your quick and really helpful reply :))))

    Antoine

    Friday, January 17, 2014 9:56 AM

All replies

  • Hi Antoine,

    According to your description, you want to calculate the average value for the total period. In this case, we can use SUM function to get the total value of Bookings Count and Day Count. And then calculate the vaerage value. Here is the sample query.

    with set bookingPeriod as [Booking Date UTCP0100].[Date].[2013-10-25]:[Booking Date UTCP0100].[Date].[2013-10-31] 
    
    member [Measures].[DayNumber] as ([Arrival Date].[Date].CurrentMember.MemberValue-[Booking Date UTCP0100].[Date].CurrentMember.MemberValue) 
    
    member [Measures].[DayCount] as [Measures].[Bookings Count]*[Measures].[DayNumber] 
    
    member [Measures].[SumBookingCount] as sum([Booking Date UTCP0100].[Date].[2013-10-25]:[Booking Date UTCP0100].[Date].[2013-10-31],[Measures].[Bookings Count])
    
    member [Measures].[SumDayCount] as sum([Booking Date UTCP0100].[Date].[2013-10-25]:[Booking Date UTCP0100].[Date].[2013-10-31],[Measures].[DayCount])
    
    member [Measures].[AvgPired] as [Measures].[SumDayCount]/[Measures].[SumDayCount] 
    
    select [Measures].[AvgPired]
    
    on 0
    
    from [Booking_Cube] 
    
    where [Arrival Date].[Date].[2013-10-31]
    

    If I have anything misunderstood, please point it out.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Friday, January 17, 2014 8:40 AM
    Moderator
  • Hi Charlie,

    I change the calculated member

    member [Measures].[AvgPired] as [Measures].[SumDayCount]/[Measures].[SumDayCount] 

    by

    member [Measures].[AvgPired] as [Measures].[SumDayCount]/[Measures].[SumBookingCount] 

    and works great !


    Thanks a lot for your quick and really helpful reply :))))

    Antoine

    Friday, January 17, 2014 9:56 AM