MDX based on actual element compared to cube value

Post Reply
Mark RMBC
Regular Participant
Posts: 164
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

MDX based on actual element compared to cube value

Post by Mark RMBC » Tue Mar 20, 2018 2:09 pm

Hi all,

Wondered if I had missed something fundamental and/or basic about mdx. I have a year dimension and the elements are 2013, 2014, 2015, 2016, 2017, 2018 etc etc etc

I have the current year held in an admin cube, so the value is currently 2017.

I want to create some mdx that includes those years which are more than 3 years older than the current year (2013 and older) and the current and previous year.

Now in order to do this I was hoping to test the year element against the admin cube value, so I wanted to say filter if the current year member is < admin cube current year value. But I can’t see anyway of filtering values based on the actual element itself, instead I could create an attribute and test the admin cube value against this, but I thought I would try to avoid this.

So is there no way of directly testing the actual element itself against a single cube value?

Incidentally the way I have got those years which are more than 3 years older than the current year and the current and previous year is as follows:

Code: Select all

{UNION(
            Except( lastperiods(99, TM1Member( [C_Year].[CYR].Item(0), 0) ), 
                        lastperiods(4, TM1Member( [C_Year].[CYR].Item(0), 0) )), 
            lastperiods(2, TM1Member( [C_Year].[CYR].Item(0), 0)))}
cheers, Mark

tomok
MVP
Posts: 2508
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 based on actual element compared to cube value

Post by tomok » Tue Mar 20, 2018 4:12 pm

Best way to do this is to create an attribute called "Date Index" and give it a value. You could even use the years themselves as the value. Then you can filter based in the Date Index. It's extremely easy.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Mark RMBC
Regular Participant
Posts: 164
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX based on actual element compared to cube value

Post by Mark RMBC » Tue Mar 20, 2018 4:16 pm

Cheers Tom, will go with your suggestion.

Mark RMBC
Regular Participant
Posts: 164
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX based on actual element compared to cube value

Post by Mark RMBC » Thu Sep 20, 2018 2:16 pm

Hi all,

just noticed that babytiger has pretty much answered my question in this thread! See here http://www.tm1forum.com/viewtopic.php?f=3&t=14251

For example I used the following to return the years greater than the Current year in the admin cube and less than 2040 (I am still struggling to allow for the 2040 to be the admin cube current year plus say 22, so 2040 is hard coded)

Code: Select all

{TM1FILTERBYLEVEL(	
		{FILTER(
			{TM1SUBSETALL([C_Year])},
			left([C_Year].currentmember.name,4)>Left([adm_Assumptions].([A_Area].[General], [AB_AdminAnalysis].[CurrentYr]),4) and left([C_Year].currentmember.name,4) < '2040' )},0)}

babytiger
Posts: 72
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: MDX based on actual element compared to cube value

Post by babytiger » Mon Sep 24, 2018 6:15 am

Nice one. :lol: :D
MK

Post Reply