TM1 Forum

Hi all,

I need to write some MDX that returns the weeks that are N level to a year in a Time dimension. For example, for 2022, I want to get 2022.Wk1, 2022.Wk2 etc. I am only interested in the N level, so for 2022, I should have 52 elements in the subset. I will then replace 2022 with pYear so it can be used dynamically in a TI.

The dim name is 'Time'.

The raw MDX generated by the Subset editor in Architect is this:
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1SubsetBasis()}, ALL, RECURSIVE )}, 0)}

Clearly I can't use that in a TI because I need to remove the TM1SubsetBasis(), but I can't work out what the answer is (and yes it's Friday arvo and I'm tired!).

Please help!

Thanks guys,

John

Got it! Used the following:
{TM1FilterByLevel(Descendants( [Time].[FY2022]) , 0)}

And boom, tish!

jv_oz wrote: ↑Fri Mar 04, 2022 3:41 am Got it! Used the following:
{TM1FilterByLevel(Descendants( [Time].[FY2022]) , 0)}

And boom, tish!

If the Weeks elements are children and only components of the fiscal year, then this will do it as well and may be a touch faster:

[Time].[FY2022].children

And in the TI:

sMDX = EXPAND('[Time].[%pYear%].children');