How to create dynamic subset for proceeding months

[wang_chris]

I have a system parameter cubes named Sys_Para, which defines current month and its index.

Cube : Sys_Para
Dimension1: Sys_Para, Members: Current_Period, Current_Period_Index
Dimension2: Sys_Para_Value, Members: Value, Notes

In the cube,
['Current_Period','Value' ] = "2014-02"
['Current_Period_Index','Value' ] =51 (which is index of "2014-02" in period dimension)


Also, there is a dimension called Period, whose member is listed in order as below.
Member Index
........
2013-01 38
2013-02 39
2013-03 40
.......
2014-01 50
2014-02 51
.......

Now I wish to create a dynamic subset in dimension Preiod starting from 6 months prior to current month shown in cube Sys_Para to next 12 months.
How can I make it?

I tried some MDX function, but can't find right method. Please help. Thanks

[declanr]

If you google (or search this forum) for "MDX Primer" you will find what is a pretty detailed explanation of how to use all the MDX functionality.

However, in your case I would just use a TI that pulls in the current date from your cube and does while loops to go back 6 months etc. Just run that TI every time you update the control cube and job done.

[wang_chris]

Thank you for your suggetsion, Declan.

I have created an TI program as your suggestion, since this subset should only be updated every month.

But for a study purpose, how can I make it in Subset definition by MDX.

According to the article '"MDX Primer', I can set dynamic subset of current month with below code:
{ TM1FILTERBYPATTERN(
{ TM1SubsetAll( [Period]) } ,
[Sys_Para].(
[Sys_Para].[Current_Period],
[Sys_Para_Value].[Value]
)

)
}

But there is only one month ----- the current month that can be displayed.

Also, I know we can set a range by below code
{ [Period].[2014-02].lag(6): [Period].[2014-02].Lead(12)}

But I have no way to replace '2014-02' with the string value obtained from cube 'Sys_para'. I tried StrtoMemebr function, but failed.

Can you or someone show me a sample to make it in MDX?

Thank you.

[EvgenyT]

Try this:

First mdx expression creates dynamic subset with current month value based on you control cube (save its as sys.CurrentPeriod):

TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, [Sys_Para].(StrToMember("[Sys_Para].[Current_Period]"),[sys_Para_Value].[SValue] ]))}

Second mdx expression references this subset in the new expression,so essential you are breaking down complex MDX syntax into 2 steps:

{ (tm1member(tm1subsettoset([Period], "sys.CurrentPeriod").item(0),0).lag(6): (tm1member(tm1subsettoset([Period], "sys.CurrentPeriod").item(0),0).Lead(12)}

Just keep in mind that if you have period dimension with consolidations e.g. months roll up to yeat etc, lag/lead function can return unexpected results. On the other hand the example I gave you above will work with flat period dimension.

Thanks

ET

[wang_chris]

It works. Thank you very much.

[EvgenyT]

Cool, you are welcome. Sometimes MDX nesting gets out of hand, so I split it into several part