MDX using OR

Post Reply
AskAndAnswer
Posts: 41
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

MDX using OR

Post by AskAndAnswer »

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!
tomok
MVP
Posts: 2831
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

Post by tomok »

Create a rollup on your time dimension called "Current and Prior Year" and reference that in the MDX.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
AskAndAnswer
Posts: 41
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Re: MDX using OR

Post by AskAndAnswer »

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.
User avatar
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

Post by paulsimon »

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
User avatar
gtonkin
MVP
Posts: 1192
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

Post by gtonkin »

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)}
AskAndAnswer
Posts: 41
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Re: MDX using OR

Post by AskAndAnswer »

Thank you for your advises. I think I will go with UNION for now and see how it works.
Post Reply