Hello,
How to filter a subset based on another cube value using OR function? I have the following statement:
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER(
{FILTER(
{TM1SUBSETALL( [Time] )},
[Time].CurrentMember.Name = Sys_Cube.([Mesure1].[Current Year], [Measure2].[String])
)},
ALL, RECURSIVE )}, 0)}
I need to update it to pull leaf elements for both Current and Prior years.
Thank you!
MDX using OR
-
- Posts: 41
- Joined: Fri Jun 02, 2017 6:35 pm
- OLAP Product: Planning Analytics
- Version: 2.0...
- Excel Version: 2016
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX using OR
Create a rollup on your time dimension called "Current and Prior Year" and reference that in the MDX.
-
- Posts: 41
- Joined: Fri Jun 02, 2017 6:35 pm
- OLAP Product: Planning Analytics
- Version: 2.0...
- Excel Version: 2016
Re: MDX using OR
The point is to have a dynamic subset that will change based on the values in the control cube. The rollup needs to be maintained.
Anyway, I created what I needed using UNION. Not sure if it's the best way, but it works.
Anyway, I created what I needed using UNION. Not sure if it's the best way, but it works.
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: MDX using OR
Hi
While you can do this using MDX to reference another cube, this can get fairly complex. Another approach is as follows:
Add an attribute to the time dimension eg Curr or Prev Year (Y/)
Then add a Rule in the }ElementAttribute_<dimension> cube that references the control cube and sets either a Y or a blank against the appropriate elements depending on whether they are within the current and previous years or not. Then to create the MDX based dynamic subset, you can just record a Filter by Attribute for the Attribute that you added and select the value Y. In general I find it easier to write the rules in the }ElementAttributes cube than to write an MDX FILTER statement based on another cube. The attribute can also be re-used in different MDX expressions, if needed. Obviously you could also take the approach of having one attribute for current year, another for prior year, and then create two subsets and union them together in the MDX.
That is not to say that this is better than Tom's approach, it is just another alternative, and both have their pros and cons. For example, although Tom's approach does involve creating a consolidation and that will cause a meta data lock, you would only need to do this once a year so that should not be too much of an issue.
Personally, for time dimensions I use a TI process that gets called during the month end rollover to update all subsets on the time dimensions, and these are just static subsets, which is generally going to give the best performance. The need to update the current year subset is just a special case of the month end rollover, when the month end rollover happens to go from one year to the next.
Regards
Paul Simon
While you can do this using MDX to reference another cube, this can get fairly complex. Another approach is as follows:
Add an attribute to the time dimension eg Curr or Prev Year (Y/)
Then add a Rule in the }ElementAttribute_<dimension> cube that references the control cube and sets either a Y or a blank against the appropriate elements depending on whether they are within the current and previous years or not. Then to create the MDX based dynamic subset, you can just record a Filter by Attribute for the Attribute that you added and select the value Y. In general I find it easier to write the rules in the }ElementAttributes cube than to write an MDX FILTER statement based on another cube. The attribute can also be re-used in different MDX expressions, if needed. Obviously you could also take the approach of having one attribute for current year, another for prior year, and then create two subsets and union them together in the MDX.
That is not to say that this is better than Tom's approach, it is just another alternative, and both have their pros and cons. For example, although Tom's approach does involve creating a consolidation and that will cause a meta data lock, you would only need to do this once a year so that should not be too much of an issue.
Personally, for time dimensions I use a TI process that gets called during the month end rollover to update all subsets on the time dimensions, and these are just static subsets, which is generally going to give the best performance. The need to update the current year subset is just a special case of the month end rollover, when the month end rollover happens to go from one year to the next.
Regards
Paul Simon
- gtonkin
- MVP
- Posts: 1211
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX using OR
Maybe another option is to use Subsets - I have a Current Year on my Year dimension and a Current Month on my Month dimension in one model. I have a cube with a 1 or a 0 for sites to indicate if they are active for publication that period. I can then filter for Sites being active during the current period using the following:
Code: Select all
{TM1Sort({FILTER({ [Site].[All N - National] },
[Active Sites].(TM1Member([Year].[Current Year].Item(0),0),TM1Member([Month].[Current Month].Item(0),0))=1)
},ASC)}
-
- Posts: 41
- Joined: Fri Jun 02, 2017 6:35 pm
- OLAP Product: Planning Analytics
- Version: 2.0...
- Excel Version: 2016
Re: MDX using OR
Thank you for your advises. I think I will go with UNION for now and see how it works.