Calculate lenght of stay in MDX

Hello,

I have to display the Bookings Count, Overnight Stay Count and Total Amount.

I have the details for the selected period

Here my query :

select{    
[Measures].[Bookings Count],
[Measures].[Overnight Stay Count],
[Measures].[Total Amount]
} 
on 0,
{    
[Arrival Date].[Date].[2013-10-01]:[Arrival Date].[Date].[2013-10-04]
} 
on 1
from [Booking_Cube]
And the result :


                       Bookings Count    Overnight Stay Count    Total Amount
2013-10-01     7300                    21471                            3374092.42
2013-10-02     7226                    19368                            3031888.06
2013-10-03     7785                    21938                            3366338.1025
2013-10-04     8950                    24842                            3961734.525

And I want to render values differently.

Days       Overnight Stay Count    Total Amount
1             140                               189010
2             105                               156800
3             90                                 120080
4             95                                 122589

...            ....                                 ....

Means there are for the selected period 140 nights when the booking is for one night, 105 nights when the booking is for 2 nights...

How can I achieve this ?

Thanks

Antoine

January 20th, 2014 4:16am

what's the grain of your fact table? is it one record per customer where the single record contains an arrival date and departure date?
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2014 6:43am

Hi Antoinepro,

In your scenario, we cannot get any relationship between the current result data you got and the expect result data. Its hard to give the solution. So please elaborate the expect result. Elaborate how it come out. Besides, you said that you want to calculate the length of stay. If in this case, you can use the DateDiff function in MDX to calculate the length between two dates. Here is a useful link for your reference.
http://bipassion.wordpress.com/2011/12/18/mdx-datediff/

If I have anything misunderstood, please point it out.

Regards,
Charlie Liao

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

January 21st, 2014 3:21am

Hello,

Thanks for your help.

I understand, there is no relationship between my query and the excepted result, I really don't know, how to start the query.

I don't have any departure date, I have only the arrival date and the number of nights (measure "overnight stay count"), and the number of booking (measure "booking count").

I will try differently :)

select {    
[Measures].[Overnight Stay Count],    
[Measures].[Total Amount]
} 
on 0
from [Booking_Cube]
where [Arrival Date].[Date].[2013-01-01]:[Arrival Date].[Date].[2013-12-31]
Overnight Stay Count    Total Amount
8145049                       109402763.58805

For this period, I have 8145049 nights and $109402763.58805

I want a row per distinct length of stay (the Overnight stay count)

a row when the booking Overnight Stay Count = 1 and the corresponding total amount

a row when the booking Overnight Stay Count = 2 and the corresponding total amount

...

Tell me if it's not clear

Thanks a lot !

Antoine

Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2014 5:11am

In that case, the easiest way to accomplish your immediate goal is to create a separate dimension (possibly a junk dimension) based on the number-of-nights measure. Then you can slice the rest of the measures by this dimension/attribute like:

SELECT 
	{    
		 [Measures].[Total Amount]
,[Measures].[Number of Nights] ,[Measures].[Number of Bookings] } ON 0, { [Dim Booking Summary].[Length of Stay].Children } ON 1 FROM [Booking_Cube] WHERE [Arrival Date].[Date].[2013-01-01]:[Arrival Date].[Date].[2013-12-31]
While you're at it, you might consider creating a new date field in the fact table derived from the number-of-nights measure. It's still not quite clear whether the number-of-nights measure is an estimated value or based on the actual check-out date...so name it accordingly (e.g. DepartureDate or EstimatedDepartureDate).

January 22nd, 2014 5:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics