Breaking monthly values down into daily values

Hi all, I'm new to MDX and am getting very confused with a script.

I'm running into problems breaking down monthly measures to daily values. If I have a monthly measure of 50, I would like to divide it by the number of days in the month to come up with daily values.

I believe I have set the proper granularity for the measurement relationship against the time dimension and have added the following script to my MDX script:

[Date].[Date].Members = [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count

When I submit a query like this:

SELECT [Measures].[Measurement Objective] ON 0,
MTD([Date].[Calendar].[Date].&[20070207]) ON 1
FROM [Cube]

Everything looks good. The query returns seven rows, each with a properly scaled version of the monthly measurement. However, when I write the following query to return a single MTD value:

SELECT [Measures].[Measurement Objective] ON 0
FROM [Cube]
WHERE MTD([Date].[Calendar].[Date].&[20070207])

It doesn't work. It gives me the error:

The MDX Function CURRENTMEMBER failed because the coordinate for the
'Calendar Year' attribute contains a set

I'm sure this is just a matter of me misunderstanding MDX. Any help would be appreciate.
Thanks,
Richard

This is caused by the fact that you have used the .CurrentMember function in your calculation, but then have used a set (the MTD function returns a set of date members) in the WHERE clause. This means that there is not a single current member.

By introducing the ability to have sets in the WHERE clause, Microsoft have added extra complexity to writing calculated members.

The following variation should do the trick:

Code Snippet

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count)

The EXISTING() function returns the set of date members in the current context (the month to date members in your example query) and the Generate() function effectively "loops" over these members calculating your original expression. In this case the [Date].[Calendar].CurrentMember is evaluated within the context of each iteration of the "loop".

|||Thanks for the reply Darren.

I tried out the script and it didn't work. I was getting a divide-by-zero error in the results. Then I tried a simpler version without the division by the number of days in the month:

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember)

which, given what you outlined, should just return the original monthly value (which is 128 for the month I'm using.) However, when I run the query each day has a value of 11891189. When I use the MTD function, it returns a concatenation of 11891189 * the number of days in the MTD set. The underlying measure is an integer.

Everything you outlined seems logical, but I'm just not sure where the 11891189 is coming from. I'm also restricting the scope of the MDX script to the single measure I'm testing.

Cheers,
Richard
|||

Actually I think I am missing a [Date] and the reference needs to be [Date].[Date].[Date].members

eg

[Date].[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].[Date].Members), [Measures].CurrentMember)

because [Date] is the dimension, [Date].[Date] is the hierarchy (which includes [Date].[Date].[All] which I think where the 11891189 might be coming from) [Date].[Date].[Date] is the actual attribute level.|||Hi Darren, it still didn't work and I've been doing a lot of investigation into the problem. I see what you mean by the problems of sets within the WHERE clause.

Instead of taking my original approach, I decided to use the Time Intelligence wizard within AS2005 and the MDX script that it generated worked like a charm. The extra YTD/MTD/QTD members in the time dimension makes a lot of sense.

Thanks again for your help
Richard