Documentation || MDX and icCube documentation

Let's break this down into small pieces. In MDX, any given year will be represented by [Time].[Year].CurrentMember . Since we know that quarters come under years in a hierarchy of time, we can find the quarters which come under a particular year by using the descendants function:

Descendants( [Time].[Year].CurrentMember, [Time].[Quarter] )

The function above gives us all the descendants, at the quarter level, which come under a given year.

Averaging the sales across these quarters is also easy:

Avg( Descendants( [Time].[Year].CurrentMember, [Time].[Quarter] ), [Measures].[Amount] )

Contribution to Total

What percentage of sales is allocated to one particular product as compared to all products?

First, get the sales of the particular product:

( [Measures].[Amount], [Product].[Prod].CurrentMember )

And now, get the sales of the parent. This can be done by:

( [Measures].[Amount], [Product].[Prod].Parent )

Calculating the contribution ratio is simply a matter of dividing the first figure by the second.

Period To Date

What are the sale figures of a particular product from the beginning of the quarter up to a specific date?

The current date can be represented by [Time].[Calendar].CurrentMember. How do you get the date for the first day of the current quarter? By looking at the time hierarchy. We know that days roll into months and months roll into quarters. So, [Time].[Calendar].Parent.Parent, would take us to the Quarter node of the current date. Now, to get to the first day of this quarter, we simply have to use the descendants function and first child function as shown below:

Descendants( [Time].[Calendar].Parent.Parent, [Time].[Calendar].[Day] ).FirstChild

To get the sum of sales from the first day of a quarter up to a specific date, do as follows:

Sum( { Descendants( [Time].[Calendar].Parent.Parent, [Time].[Calendar].[Day] ).FirstChild : [Time].[Calendar].CurrentMember }, [Measures].[Amount] )

Next chapter: Calculated Members.