I need to achieve something that is very simple using T-SQL, and am
vomitting blood now with MDX ... :'(

Given a date, I need to find the "First Day Two Quarters" ago,
e.g. for 25-Jul-2006, in Q3 (using Calendar Quarter), the expected
result is 01-Jan-2006 (first day of Q1 2006);
for 05-May-2006, the expected result is 01-Oct-2005.


Using Adventure Works cube, I have constructed with this MDX

Select [Measures].[Internet Sales Amount] on columns,
{ [Date].[Date].CurrentMember.Children } on rows
from [Adventure Works]
where {[Date].[Calendar].[Date].&[400].Parent.Parent.Lag(2)}

This works fine, except that it is returning all days in that Quarter.
And I trully want only the first day of that.

I have tried
- [Date].[Date].CurrentMember.Children.FirstChild
- [Date].[Date].CurrentMember.FirstChild
- [Date].[Date].CurrentMember.Children.Parent.FirstChild
- [Date].[Date].CurrentMember.Parent.FirstChild
and all failed to work.

What am I doing wrong?

thanks in advance.

Take a look at the parallelperiod function and firstsibling function.

something like:
Select [Measures].[Internet Sales Amount] on columns,

{

parallelperiod([Date].[Calendar].[Calendar Quarter]

,2,[Date].[Calendar].[Date].&[400]).firstsibling

, [Date].[Calendar].[Date].&[400]} on rows

from [Adventure Works]





"Rose" <rose.say (AT) gmail (DOT) com> wrote

You are pretty close. The problem is that the WHERE clause in MDX is
really meant to be used to filter dimensions that are not part of the
visible axis. If you want to filter the member on an axis, you would
filter them inline.

eg

Select [Measures].[Internet Sales Amount] on columns,
{[Date].[Calendar].[Date].&[400].Parent.Parent.Lag(2)} on rows
from [Adventure Works]

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1153817215.507681.155640 (AT) h48g2000cwc (DOT) googlegroups.com>,
rose.say (AT) gmail (DOT) com says...