Excel: Element of Subset?

Post Reply
Mark2007
Posts: 45
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Excel: Element of Subset?

Post by Mark2007 »

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
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: Excel: Element of Subset?

Post by tomok »

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: 45
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Re: Excel: Element of Subset?

Post by Mark2007 »

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: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Excel: Element of Subset?

Post by paulsimon »

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: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Excel: Element of Subset?

Post by Alan Kirk »

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: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Excel: Element of Subset?

Post by paulsimon »

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: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Excel: Element of Subset?

Post by Alan Kirk »

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: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Re: Excel: Element of Subset?

Post by John Hammond »

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.
Mark2007
Posts: 45
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Re: Excel: Element of Subset?

Post by Mark2007 »

Dear all,

thanks a lot for your help and comments. what finally worked:
ELISCOMP(DimName, ElementName, Subname)
this works in TM1Web, too,

Thanks especially John for this idea!

Regards

Mark
Post Reply