Hi All,
I am new to MDX .We need to measure driver count against time.

Measure level
Driver Count
Year All time 12
1920
....

2003
q1 Quarter 1 total 5
Jan 10
Feb 20
Mar 5

Q2 Quarter2 total 12
April 7
May 12
June

Q3

..

2010


For Q1 total we should get last month value for that quarter.
If the last month value is empty, it should display the nonempty value in
that quarter. Now we are

getting driver count value if some value exits for last month in that
particular Quarter. Otherwise it

is displaying empty.

For the year Total it should display Q4 last month value. If it is empty, it
should show nonempty value

from non empty previous quarter’s non empty last month value.

For the All time value, we should get last year (2010 in our case), Q4 last
month value if it is not

empty. If it is empty, the value of non empty previous year’s last quarter
non empty value.

How to write MDX in calculated member for the above scenario?

Any help would be great.

Thanks
Suchi

This Microsoft paper discusses closing balances in MDX:

http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/semiadd2.asp
A common real-world problem can arise during implementation of the
closing balance expressions. In many implementations, the Time dimension
is defined with future time period members already contained in the
dimension. The ClosingPeriod([Month]) function does not analyze future
and past time periods. It only traverses the members' hierarchy tree to
find the last leaf descendant under the given member.

For example, take the example of implementing a closing balance
expression with a current date of mid-October 1998. Asking for the
closing balance for 1998 arguably should provide the data from the last
snapshot of the year, or the October snapshot. However, the
ClosingPeriod([Month]) function returns December 1998 as the closing
period of 1998. Because no snapshot exists for December 1998, the
closing balances return NULL.

This more sophisticated expression solves the problem:

Measures.[Last Non Empty Value]:
IIf(IsEmpty((Measures.[Value], Time.CurrentMember) ,
(Measures.[Last Non Empty
Value],Time.CurrentMember.PrevMember),Measures.[Value])
Measures.[Closing Value]: (Measures.[Last Non Empty Value],
ClosingPeriod([Month]))

In this example, the [Last Non Empty Value] measure has a recursive
expression that checks to see whether the value of the current cell is
empty. If it is empty, the expression moves back to the previous period
on the time dimension and checks the [Last Non Empty Value] of the
previous period. The function continues to go back in time until a
nonempty value is found. The [Closing Value] measure can then use the
[Last Non Empty Value]. This recursive behavior helps ensure that, for
the last year, the values of the last snapshot are returned. The less
common CoalesceEmpty() function performs this exercise more efficiently.
This function is equivalent to the expression demonstrated earlier:

Measures.[Last Non Empty Value]:
CoalesceEmpty((Measures.[Value], Time.CurrentMember ) ,
(Measures.[Last Non Empty Value],Time.CurrentMember.PrevMember))
...

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!