Excel: Element of Subset?

[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]

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.

[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"

[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

[Alan Kirk]

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.

[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

[Alan Kirk]

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.

[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]

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