MDX: Rolling YTD

Hi,
I have been searching for a solution in MDX to create rolling
Year-to-Date calculated members.
For example:
When the current member time is June 2006, I want the total sales YTD
of last year, that
means from January 2005 till June 2005.
Thanks for any hints for a solution
RS.
ps.
My starting point was:
sum({ytd()},[Measures].[Sales])
But this formula is the sum YTD for this year. I'm searching for a
formula for the sum YTD of last year.
I've tried every combination with sum, OpeningPeriod(), lag,
ParallelPeriod().RS wrote:
> Hi,
> I have been searching for a solution in MDX to create rolling
> Year-to-Date calculated members.
>
> For example:
> When the current member time is June 2006, I want the total sales YTD
> of last year, that
> means from January 2005 till June 2005.
>
> Thanks for any hints for a solution
>
> RS.
>
> ps.
> My starting point was:
>
> sum({ytd()},[Measures].[Sales])
>
> But this formula is the sum YTD for this year. I'm searching for a
> formula for the sum YTD of last year.
>
> I've tried every combination with sum, OpeningPeriod(), lag,
> ParallelPeriod().

> I datagrep is offer the solution for most of the problems
> so visit this for ur solutions
>http://www.datagrep.com

MDX Set Expression Converts to Constants (and I don't want this to happen)

First, I should mention this problem is occuring in Proclarity 6.1 with an underlying SSAS 2005 cube.

We want a rolling 12 month window displayed in our chart. I have written:

SELECT { Tail(Filter([Time].[Month].Members,Not IsEmpty(Time.CurrentMember)),12)} ON COLUMNS ,

Which works just fine when I enter it. But when the Proclarity book is altered in anyway (say, by changing from one measure to another), this MDX expression is converted ("instantiated" ? is that the right word ?) to constants:

SELECT { [Time].[All Time].[2005].[Quarter 3].[August], [Time].[All Time].[2005].[Quarter 3].[September], [Time].[All Time].[2005].[Quarter 4].[October], [Time].[All Time].[2005].[Quarter 4].[November], [Time].[All Time].[2005].[Quarter 4].[December], [Time].[All Time].[2006].[Quarter 1].[January], [Time].[All Time].[2006].[Quarter 1].[February], [Time].[All Time].[2006].[Quarter 1].[March], [Time].[All Time].[2006].[Quarter 2].[April], [Time].[All Time].[2006].[Quarter 2].[May], [Time].[All Time].[2006].[Quarter 2].[June], [Time].[All Time].[2006].[Quarter 3].[July] } ON COLUMNS ,

Does anyone know how to prevent this ?

Using a named set for your time expression will prevent this. In ProClarity Desktop Professional, create a named set using your time expression, next use the named set in your query. Now save the results of this query to a briefing book.|||

gbraach:

Thanks for your assistance. I did follow your advice, and created a named set. ("Rolling12Months"). Then, my select statement becomes:

SELECT { [<##<SET!DASCOTT!Rolling12Months>##>] } ON COLUMNS ,

Which again works just fine, but unfortunately becomes constants when I change the measure from fees to record count. Thus:

SELECT { [Time].[All Time].[2005].[Quarter 3].[August], [Time].[All Time].[2005].[Quarter 3].[September], [Time].[All Time].[2005].[Quarter 4].[October], [Time].[All Time].[2005].[Quarter 4].[November], [Time].[All Time].[2005].[Quarter 4].[December], [Time].[All Time].[2006].[Quarter 1].[January], [Time].[All Time].[2006].[Quarter 1].[February], [Time].[All Time].[2006].[Quarter 1].[March], [Time].[All Time].[2006].[Quarter 2].[April], [Time].[All Time].[2006].[Quarter 2].[May], [Time].[All Time].[2006].[Quarter 2].[June], [Time].[All Time].[2006].[Quarter 3].[July] } ON COLUMNS ,

I'm still stumped.