Excel: Element of Subset?

Post Reply
Mark2007
Posts: 18
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: Excel 2010

Excel: Element of Subset?

Post by Mark2007 » Fri Feb 02, 2018 3:44 pm

Hi there,
is there a possibility to get the information (in Excel or in TM1Web, not via TI):

Element xyz is part of Subset abc?

Thanks in advance!

Mark

User avatar
tomok
MVP
Posts: 2378
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: Excel: Element of Subset?

Post by tomok » Fri Feb 02, 2018 5:39 pm

Mark2007 wrote:
Fri Feb 02, 2018 3:44 pm
Hi there,
is there a possibility to get the information (in Excel or in TM1Web, not via TI):

Element xyz is part of Subset abc?
You've heard of the SUBNM function, right? Simply supply it with the dimension, subset, and element name. If the element is not part of the subset then it will return blank. If it is a member it will return the member name. Like this:

SUBNM("Planning Sample:Departments","Corporate","Marketing")

If Marketing is part of the Corporate subset then the function returns Marketing, otherwise the function returns nothing.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Mark2007
Posts: 18
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: Excel 2010

Re: Excel: Element of Subset?

Post by Mark2007 » Fri Feb 02, 2018 6:20 pm

Thanks for the quick response and the good idea. I have to say, that in my case (TM1 10.1., Excel 2013) it does not work. Even if the element is not part of the subset, the subnm-formula delivers the name:

SUBNM("Planning Sample:Departments","Production","Marketing")
delivers "Marketing", even if "Marketing" is not part of the subset "Production" :-(

User avatar
paulsimon
MVP
Posts: 556
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Excel: Element of Subset?

Post by paulsimon » Fri Feb 02, 2018 7:35 pm

Hi

At least in 10.1 and for that matter in 10.3, if the element is a valid element in the dimension then SUBNM will return the element regardless of whether it is in the subset. If you want to ensure that the SUBNM displays an element that is in the subset then use the number position 1 instead of the element name form of the SUBNM.

If you want to check that what the user picked is in the subset then another options would be using VBA to loop through all elements in the subset using the position number in place of the name to check that the element chosen is in the subset. However, that is not possible if you are using TM1 Web since you can't use VBA

To a certain extent, if you want to ensure that the user can only pick an element from the subset then anything based on SUBNM won't work anyway as the user can always show all elements and pick anything they want in the dimension.

An alternative is to use a hidden sheet with numbers dragged down way passed the maximum number of elements that you are likely to get in the subset, and then use a SUBNM referencing the number and fill this down to get all elements in the subset. Then define a range name on this. On the main sheet instead of a SUBNM use Excel's Data Validation to reference the list. Then the user will get a pull down list of elements in the subset and cannot choose anything that is not in the subset.

If a user is only ever allowed to see certain elements, you could also consider ElementSecurity as a way to limit what they can see.

Regards

Paul Simon

User avatar
Alan Kirk
Site Admin
Posts: 5760
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Excel: Element of Subset?

Post by Alan Kirk » Fri Feb 02, 2018 8:19 pm

paulsimon wrote:
Fri Feb 02, 2018 7:35 pm
At least in 10.1 and for that matter in 10.3, if the element is a valid element in the dimension then SUBNM will return the element regardless of whether it is in the subset.
9.5.2 and 10.2.2 for that matter as well. This is why a SubIx function has long been asked for.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
paulsimon
MVP
Posts: 556
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Excel: Element of Subset?

Post by paulsimon » Sat Feb 03, 2018 12:21 pm

Hi Alan

Probably most of us have written our own VBA Add-In to do those functions that are present in TI but absent from the Worksheet such as SUBIX, ELISANC, etc. Unfortunately, since VBA cannot be used on TM1 Web it would be good to see these incorporated in the product by IBM. However, for the moment to help the person who raised the request we would need to know why they wanted to check whether something existed in the subset. Perhaps one of the other suggestions like security will get them past their immediate issue.

Regards

Paul Simon

User avatar
Alan Kirk
Site Admin
Posts: 5760
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Excel: Element of Subset?

Post by Alan Kirk » Sat Feb 03, 2018 6:33 pm

paulsimon wrote:
Sat Feb 03, 2018 12:21 pm
Probably most of us have written our own VBA Add-In to do those functions that are present in TI but absent from the Worksheet such as SUBIX, ELISANC, etc. Unfortunately, since VBA cannot be used on TM1 Web it would be good to see these incorporated in the product by IBM. However, for the moment to help the person who raised the request we would need to know why they wanted to check whether something existed in the subset. Perhaps one of the other suggestions like security will get them past their immediate issue.
Paul, I wasn't questioning your answer (which was spot on), just adding that the reported behaviour covers all recent versions, and that this is a known omission from the program. (Although it was eventually finally addressed in TI at least by SubsetElementGetIndex, which was added in PA.) At this stage of the game I'll be a little surprised if it's ever added to Perspectives, given the short projected shelf life of that.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

John Hammond
Community Contributor
Posts: 258
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: TM1/PM/Cafe
Version: 10.2
Excel Version: 2010
Location: City of London

Re: Excel: Element of Subset?

Post by John Hammond » Tue Feb 06, 2018 2:55 pm

IIRC

ELISANC(dimname, subname, elementname)

works in Excel but nowhere else as does ELISPAR.

Otherwise use SUBNM with index 1,2 to iterate the subset on a hidden page and do VLOOKUP.

Post Reply