How to create dynamic subset for proceeding months

wcb79

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

wyjzcom

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.

sunnypure

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.

奪命排骨飯

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

gaohua5201

It works. Thank you very much.

赏狗网

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