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
MDX for Weeks in a Year using a Parameter for Year
-
- Posts: 13
- Joined: Fri Oct 13, 2017 12:44 pm
- OLAP Product: TM1
- Version: PA 2.0.2
- Excel Version: 2016 64 bit
Re: MDX for Weeks in a Year using a Parameter for Year
Got it! Used the following:
{TM1FilterByLevel(Descendants( [Time].[FY2022]) , 0)}
And boom, tish!
{TM1FilterByLevel(Descendants( [Time].[FY2022]) , 0)}
And boom, tish!
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: MDX for Weeks in a Year using a Parameter for Year
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');
Ty
Cleveland, TN
Cleveland, TN