Relative date 'Rolling 3 months' --help please

Hi,

I need to add special relative date categories in SSAS

that similar to the functionality offered by Cognos/Powerplay. With Cognos, you

can create relative time categories very easily-- like ‘Rolling 3 months’,

‘Prior Rolling 12 Months’ etc.

I created Time Dimension with SSAS BI Studio, added a new

named calculation ‘Rolling 3 Months’ to the Time Dimension. Below is

calculation code:

CREATE MEMBER

CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling

3]

AS

null,

VISIBLE = 1;

Scope(

{

[Measures].[ORDERS]

}

);

// Rolling 3 Months

(

[Time].[Rolling 3

Months].[Rolling 3],

[Time].[Month].[Month].Members

) =

Tail([Time].[Month].members ,3 );

End Scope;

I uesd Tail() function in SS Management studio, it got

the result I want. But it doesn’t work in SSAS BI Studio.

Am I missing something here? Or any suggestions would be

greatly appreciated.

Thanks.

Your syntax for the expression is wrong - you assign set where the numeric value is expected. I suggest that you use built-in Time Intelligence wizard - it supports expressions for Rolling 3 months. It would be something like

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members) = Aggregate( [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember);

|||Thank

you very much for the help.

I realized something wrong here, but I just do

not know how to correct it. Thank you very much for point out. I tried Time

Dimension wizard, there are ‘month to date’ and ‘Three

Month Moving Average’ in Time Dimension wizard, but our project ask from Cognos Powerplay cube move to SSAS cube. in Powerplay,

‘Rolling 3 months’ will get each month order number for 3 months.

Should be like:

Month|

Orders

January, 2007|12345

December, 2006|14567

November, 2006|33562

Not 3 months Moving

Average. We expect get same

result like powerplay. Is there a way to get this result?

Thank you very much for the help, I really appreciated.

|||Please use the expression provided above.|||Hi Mosha,

Yes. I used the expression you provided above, but got "#VALUE!".
Rolling 3 months | Orders
-
Current Periods | 235263
Rolling 3 months | #VALUE!

What else I can do?

Thanks a lot.
|||Please provide the query that you sent, the exact MDX Script you have and the text of error message in order to find where is the problem.|||The code in BI studio script view:

First try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS ([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
),
VISIBLE = 1 ;

Second try:

CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember,

VISIBLE = 1 ;

Scope(
{
[Measures].[ORDERS]
}
);

// Rolling 3 Months
([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

End Scope;

Got same result in cube browser:

Rolling 3 Months | Orders
-
Current Periods | 123456
Rolling 3 | #VALUE!

no error message or say '#VALUE!' is an error message.

What's wrong?

|||

You still kept the portion with the error. You also need to decide what do you want to do at the levels above Month. I will put the code which keeps it NULL at such levels - up to you what to do there.

CREATE [Time].[Rolling 3 Months].[Rolling 3];

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

|||Thanks a lot Mosha.

I put the code you provided in to script view.
CREATE [Time].[Rolling 3 Months].[Rolling 3];

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

result:
[Rolling 3] row is missing.

Rolling 3 Months | Orders
--
Current Periods | 12345

Currently, we just want to get each month's order for last three months with 'Rolling 3 Months' this calculated member.
I am not sure your question for 'at the levels above Month', you mean Year level?
If I need Year level, how to add in?

am I missing some point?

Thanks a lot.
|||Yes, I meant on the Year level. It is not clear how you want to aggregate rolling 3 months to the Year. Anyway, you in order to see rolling sum on 3 month, please slice by some specific month, or add months to the axis.|||Thanks Mosha for the help.

I add specific month, but result some like before -no 'Rolling 3' row.

CREATE [Time].[Rolling 3 Months].[Rolling 3] ;

([Time].[Rolling 3 Months].[Rolling 3],[Time].[Month].[November 2006], Measures.[ORDERS]) = Aggregate(
[Time].[Month].[November 2006].Lag(2): [Time].[Month].[November 2006]
);

Something still wrong?

Thanks.|||The expression in the MDX script should stay the way it was before. It is your MDX query that needs to change to include slice by month.|||

As my understanding(may be wrong), MDX

script run in BI Studio, MDX query run in SS management Studio. In SS

management Studio, I used following MDX query:

SELECT Tail(

[Time].[Month].members,3 ) ON rows,

[Measures].[ORDERS] on columns

from [My cube]

Got

‘Rolling 3 Months’ data I want.

Month|

Orders

October

2006|12345

November

2006|34556

December

2006|23545

In BI Studio, if using filter by Month, it still able

to get ‘Rolling 3 Months’ result.

The

problem is using filter by Month, there are too many steps, our business user

will not accept that. They hope only drag ‘Rolling 3 Months’ from Time dimension drop to the cube browser then

can get above data (like Powerplay does). Is there a way to do that?

How to add MDX query in BI Studio?

Thanks for the help..