Page 1 of 1

Excel: Element of Subset?

Posted: Fri Feb 02, 2018 3:44 pm
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

Re: Excel: Element of Subset?

Posted: Fri Feb 02, 2018 5:39 pm
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.

Re: Excel: Element of Subset?

Posted: Fri Feb 02, 2018 6:20 pm
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" :-(

Re: Excel: Element of Subset?

Posted: Fri Feb 02, 2018 7:35 pm
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

Re: Excel: Element of Subset?

Posted: Fri Feb 02, 2018 8:19 pm
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.

Re: Excel: Element of Subset?

Posted: Sat Feb 03, 2018 12:21 pm
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

Re: Excel: Element of Subset?

Posted: Sat Feb 03, 2018 6:33 pm
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.

Re: Excel: Element of Subset?

Posted: Tue Feb 06, 2018 2:55 pm
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.

Re: Excel: Element of Subset?

Posted: Mon Feb 19, 2018 6:25 pm
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