Creating a dynamic YTD months subset

[tutak]

Hi,

I have a time dimension which looks like below and I have a cube which holds Current YTD value (Feb 2010 YTD in this case). Using this cube I want to create a subset including only the YTD months (in this case it will be only Jan 2010 and Feb 2010). Any ideas would be really appreciated? What kind of an TI process can I build to create this subset.

Thanks

Dec 2010 YTD
-Nov 2010 YTD
--Oct 2010 YTD
---Sep 2010 YTD
----Aug 2010 YTD
-----Jul 2010 YTD
------Jun 2010 YTD
-------May 2010 YTD
--------Apr 2010 YTD
---------Mar 2010 YTD
----------Feb 2010 YTD
-----------Jan 2010 YTD
-----------Jan 2010
----------Feb 2010
---------Mar 2010
--------Apr 2010
-------May 2010
------Jun 2010
-----Jul 2010
----Aug 2010
---Sep 2010
--Oct 2010
-Nov 2010
Dec 2010

[Kyro]

You could potentially use:

Code: Select all

sCurMonth = CELLGETS(...);

DimName = 'Month';
SubName = 'Current YTD Months';
IF( SubsetExists(DimName, SubName) = 1 );
    SubsetDestroy(DimName, SubName);
ENDIF;
SubsetCreateByMDX(SubName,'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( { [' | DimName | '].[' | sCurMonth | ']  }, ALL, RECURSIVE )}, 0)}');

(written on the forum & untested)

[tutak]

Thanks a lot Kyro, worked like a charm. Really appreciate it.