|
Home > Archive > MS SQL Server OLAP > December 2005 > AS 2005 MDX ParallelPeriod used in calculated member
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
AS 2005 MDX ParallelPeriod used in calculated member
|
|
| charles 2005-12-29, 9:23 am |
| Hello all,
I want to create a calculated member to get inventory of a specified
period, such as 1 month ago, 1 quarter ago, 1 year ago. I use AS2005
and have a date dimension with Date hierarchy as follows
Date
Year
Quarter
Month
Day
Assume the current date memeber is Decemeber 25, 2005
1. ParallelPeriod( [Date].[Date].[Year], 1,
ClosingPeriod([Date].[Date].[Day], [Date].[Date].[CurrentMember])
2. ParallelPeriod( [Date].[Date].[Quarter], 1,
ClosingPeriod([Date].[Date].[Day], [Date].[Date].[CurrentMember])
3. ParallelPeriod( [Date].[Date].[Month], 1,
ClosingPeriod([Date].[Date].[Day], [Date].[Date].[CurrentMember])
The 1st returns Decemeber 25, 2004, RIGHT
The 2nd returns the first day of the date dimension, WRONG
The 3rd returns the first day of the date dimension, WRONG
it's not clear to me why the 2nd and 3rd are not right. Could any
expert help me figure out the reason?
Thanks in advance.
Charles
| |
| Deepak Puri 2005-12-30, 3:23 am |
| Hi Charles,
Not sure how your Date dimension is structured, but you can compare it
to Adventure Works, where this works:
[color=darkred]
select {[Measures].[Order Count]} on columns,
ParallelPeriod([Date].[Calendar].[Month], 1,
ClosingPeriod([Date].[Calendar].[Date],
[Date].[Calendar].[Date].[December 25, 2003])) on rows
from [Adventure Works][color=darkred
]
The result on rows is [November 25, 2003]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.droptable.com ***
|
|
|
|
|