mdx: retrieve the number of days between two different date dimensions

Question

I'm working on a cube with two different date dimensions: the fist one manages the patient hospedalization dates: [hosp_date] it is organized in Year,quater, month and relative_date : this one counts the number of days since 1st Jan. 1980.

and the second one manages the patient discharge dates: [disc_date] with the same organization of the first one.

these two dimensions are full, no holes between two days.

In this cube there is only one measure that count the number of the hospedalization. I'd like to create a calculated member with the number of days of hospedalization, so using the relative date something like:

with member [Measures].[dd] AS '[disc_date].[relative_date].currentmember - [hosp_date].[relative_date].currentmember'


select {[Measures].[dd]} on 0,
{[patient].[patient_id].members} on 1
from [cube]

And this query did not work as i thought. It returns the difference between the number of hospedalizations that ends on disc_date and the number of hospedalizations that starts on hosp_date. So it returns 0 for every patient. How can i retrieve the no. of hospedalization days for every patient?

tnx for reading.

Rudolf Rudolf · Accepted Answer · 2010-01-07T15:34:59

In fact, what happens is that during the calculation of your calculated measure, MDX takes the default measure of your cube because you haven't specified one.

Also, for your 2 [relative_date] levels in the dimension there are no members specified in the query so the default members are taken (probably the [all] member). Therefore the result is always 0.

A possible solution is adding the 2 relative_date levels from your dimensions as measures to the cube. Then use these new measures in your calculation.

mdx: retrieve the number of days between two different date dimensions

1 Answers