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

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.

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