YTD values vs. Year

#1

I'm constructing a utility/periodicity dimension that will hopefully
provide time-based data cuts at various intervals. At the moment, I've
created a YTD() calculated member, as well as a prior-YTD() calculated
member: both work just fine.

But, I'm struggling with the following: If I have a cube with data from Jan
2003 through June 2004, and a user selects a month time-slice between
Jan'04 and May'04 in a client app, what is the MDX that will display not
only YTD() and Prior YTD(), but also a year's value based on the current
month time-slice?

For example, if a user selects March 2004, even though there is data
through June 2004, I would like the user to see both the YTD() values
(i.e., Jan, Feb, March of 2004) as well as the total for the year of the
current slice (i.e. 2004). Consequently, the 2004 year's values would
always be the same regardless of end-user's slice selection: i.e., only the
YTD() calculation should be affected by the end-user's selection. Of
course, if the end-user merely chooses "2004", then the YTD() and the
"year's values" would be equal.

I've struggled to make use of time.currentmember.parent along with
Descendants(). It makes sense to me to use the .parent when at the month
level, but not at the year level.

Any suggestions?

Thanks,
Peter

--
Message posted via http://www.sqlmonster.com

#2

Hi Peter,

Since I'm not sure how your utility dimension is named, here's an
example using names from this [MS} article:

http://support.microsoft.com/default...b;en-us;304118

Quote:

INF: How To Perform Time Series Calculations

...
Create a new dimension, named "Periods", by using the Periods table as
the data source. Save, and then process the new dimension.
8. Open the Sales cube for editing, and then add the Period dimension.
9. Create a new calculated member with the following properties:• Member
Name = "Year To Date Sum"
• Parent Dimension = "Periods"
• Value = SUM(YTD(),[PERIODS].&[1])
...

Quote:

So, the total for the current year would be like:

Quote:

With Member [PERIODS].[CurrentYear] as

'(Ancestor([MyTime].CurrentMember, [MyTime].[Year]),
[PERIODS].&[1])'

Quote:

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***