Websheet function SUBNM shows a duplicate entry

[yuviappu]

Hi,

I dont have much experience with Prespectives. So, Sorry if my question sounds silly.

We are using an Active Form in which a cell is using the websheet function SUBNM. It was working fine for a long time but now it shows a duplicate entry when I click on the cell for selection in Websheet. I dont find anything unusual in the dimension elements and am not sure how an element appears twice. All of its child elements are also appearing twice.

Someone please let me know if I am missing something.

Thanks!

Regards,
Appu

[David Usherwood]

There is nothing stopping subsets having duplicates - someone has probably changed the subset in a way which doesn't suit you. Selecting Hierarchy Sort (on the right of the Descending Sort icon) will dedupe the subset - then update it.

[yuviappu]

Thanks for the response David!

But, I see there is no subset name is mentioned in the SUBNM function and Alias is mentioned. In the mentioned Alias, when I checked in Architect, does not give any duplicates.

Regards,
Appu

[jim wood]

There may have been duplicates in the subset when the form was created. I would create a subset that you know does not have duplicates then insert that in to the SubNm.

[yuviappu]

Hi Jim,

The websheet did not show those duplicate entries since after development. Or, it was not noticed by us. As it was reported by a user, suddenly it became a matter for discussion. However, May I know what I could do to resolve it?

Thanks!

Regards,
Appu

[Alan Kirk]

The websheet did not show those duplicate entries since after development. Or, it was not noticed by us. As it was reported by a user, suddenly it became a matter for discussion. However, May I know what I could do to resolve it?

I'd be most interested in seeing a screenshot of the actual formula in the source Excel sheet. Then I'd be interested to see a screenshot showing this alleged duplication.

[yuviappu]

Hi Alan,

As It is a controlled environment, I could not capture the screenshot of it. So, I am writing the formula given in the websheet here.

=SUBNM(Server_Name&":Dimension","","ElementName","AliasName")

About the websheet, I try to explain the issue below.

When I first search for the "Parent 1", I get the below result in the pane. Please note that these leaf elements have to come under another consolidation under Parent 1.

+Parent 1
-Child 1
-Child 2
-Child 3
-Child 4

When I expand Parent 1, I expected only one instance of Parent 2. But, I see a repeated entry with children.

-Parent 1

-Parent 2
-Child 1
-Child 2
-Child 3
-Child 4

-Parent 2
-Child 1
-Child 2
-Child 3
-Child 4

Sorry, this is all I could do instead of a screenshot.

Thanks!


Regards,
Appu

[Alan Kirk]

As It is a controlled environment, I could not capture the screenshot of it. So, I am writing the formula given in the websheet here.

=SUBNM(Server_Name&":Dimension","","ElementName","AliasName")

If this is really the formula, it should yield an element list which is the same as you would get by selecting the [All] button in subset editor.

About the websheet, I try to explain the issue below.

When I first search for the "Parent 1",

Aaaaand there we go. You have not mentioned anything about doing a "search" before. much less the fact that you are expanding some of the elements. The "problem", such as it is, isn't with the original non-specified subset (which can't have duplicates since it's the same as "All"), but with what you then do with it.

Since my environment isn't "locked down", let me illustrate.

This is a dimension which has no Default subset. Therefore when I open it it launches the equivalent of the All subset, just as your formula should do. Note the absence of duplicates.

29-01-2016_Subset1.jpg (65.48 KiB) Viewed 9732 times

Now I shall expand the Weekdays consolidation.

29-01-2016_Subset2.jpg (69.76 KiB) Viewed 9732 times

Lord have mercy, all of the days from Monday to Friday are duplicated!!!!

No, they aren't. They appear under two different consolidations, so when I expand the second consolidation, yes, they will be shown twice. That's because that's what I told Subset Editor to do. The elements are not in the dimension twice, they're not duplicated, they are simply shown in the two places that they appear under.

(Or more precisely two of the places that they appear under; since a dimension can have multiple hierarchies they might appear under many consolidations. And every time I expand one of the consolidations that they appear under, they will appear again. This isn't a bug; it's just me expanding a consolidation to see its details.)

And this is almost certainly what is happening with yours. Parent 2 will be part of the original list of elements; as I said, that list will show all. As soon as you expand Parent 1 it will be shown again simply because you told Subset Editor "Show me what appears under Parent 1". What it will not do is remove the original listing of Parent 2. Hence you see it twice but it is not a duplication.

[yuviappu]

Thanks for the detailed explanation!

- Parent 1
+ Parent 2
+ Parent 2

Above scenario is also usual? I understand that Parent 2 can appear under other consolidations too. But, under same parent it appears twice in websheet. Also, when I do expand-collapse-expand, everything becomes fine with only one Parent 2 under Parent 1.

Thanks!

Regards,
Appu

[Alan Kirk]

Thanks for the detailed explanation!

- Parent 1
+ Parent 2
+ Parent 2

Above scenario is also usual? I understand that Parent 2 can appear under other consolidations too. But, under same parent it appears twice in websheet. Also, when I do expand-collapse-expand, everything becomes fine with only one Parent 2 under Parent 1.

You would not have the situation of Parent 2 being under Parent 1 twice at the same level the way you have illustrated above. Try adding the same element to a consolidation twice and see how far you get. (Hint: Not very.) You can, however, have it appearing in a subset as many times as you want.

Accordingly what can happen is something like this. (The structure of this dimension has been deliberately done badly to make the point.) Note LFL Stores, which appears below All Stores.

29-01-2016_Subset3.jpg (85.13 KiB) Viewed 9688 times

Now I expand All Stores, to reveal its children, one of which is LFL Stores.

29-01-2016_Subset4.jpg (107.25 KiB) Viewed 9688 times

Is LFL Stores under All Stores twice? No. The green line line leading down from All Stores to LFL Stores indicates a relationship; the items that are joined to the top element are under that element.

The LFL Stores entry below that does not have a line leading to it. That means that the second occurrence of LFL Stores just part of the list. It is not indicating any relationships with other elements.

The fact that an element appears lower down in the subset list than another element does not imply that the lower one is "under" the upper one. That's only true if there is a descending line.

Conversely, the fact that an element may appear in a subset list multiple times does not imply that that it rolls up to a parent multiple times.

[yuviappu]

Hi Alan,

I really wished any of your points have worked to prove my understanding was wrong. Unfortunately, I still have to stick to my issue description. All your points are correct if I check the scenario in subset editor.

It appears twice in websheet alone. Sorry I could not get you any more details to help me.

Thanks!

Regards,
Appu

[Alan Kirk]

I really wished any of your points have worked to prove my understanding was wrong. Unfortunately, I still have to stick to my issue description. All your points are correct if I check the scenario in subset editor.

It appears twice in websheet alone.

Which "issue description" would that be, exactly? The one where you said:

"it shows a duplicate entry when I click on the cell for selection in Websheet"?

Or the one where you contradicted that and said it happened "When I first search for the "Parent 1"" and "When I expand Parent 1"?

Or the one where you're now saying "It appears twice in websheet alone", whatever that is supposed to mean?

Sorry I could not get you any more details to help me.

The reality is that it's pointless discussing this unless you can actually show, in a screenshot, what it is that you're referring to. This sort of thread, where the description of the problem changes with each post, is exactly what the Request For Assistance Guidelines are intended to avoid.

[yuviappu]

Hi Alan,

Thanks for suggesting the Guidelines! However, I could not provide the screenshots due to the nature of my technical environment. I will update this post if I find any work around.

Thanks again for your support!

Regards,
Appu.

[qml]

Thanks for suggesting the Guidelines! However, I could not provide the screenshots due to the nature of my technical environment.

What does that mean, exactly? Is your PrtScn key broken? You have already wasted a lot of other people's time by providing poor and contradictory description of your problem. Now you're unwilling to provide a screenshot or any other illustration (how about a sketch in MS Paint?), or even just answer the questions of the people trying to help you against all odds. It looks like you did not want any help, you just wanted to kill some time.

[gtonkin]

Pro Tip - use your smartphone to capture a screenshot!

BTW - it may be as simple as set the sort order to Hierarchy, Name. If you have set to manual for a reason then you need to observe the rules when saving i.e. ensure that you expand and collapse elements in the correct sequence to avoid a mess.

[yuviappu]

Hi All,

What I mean as Controlled Environment is the scenario below.

1. Camera mobiles are not allowed inside the work area.
2. Yes, PrintScreen will not work. It is disabled.
3. I can not copy anything from the TM1 Servers to local machines, personal mails.

Most of the banking projects function this way to ensure no data is leaked out or at least in the projects that I worked so far. As I did not want to embarrass more by explaining again(I know it is poor as I am new to TM1 and I understand it is difficult without a screen capture), I thought of stopping my posts.

Thanks for your time!

Regards,
Appu