Hierarchy mid-levels being double displayed in graphs

[#1]

Hi all -

I've been working on an issue for a while and it's driving me crazy that I can't figure out what's wrong.

Right now we have a hierarchical field in our data that we would like to display as a tree view.  The hierarchy is as follows:

Sector (highest level) --> Segment (mid level) --> Subsegment (lowest level)

So we have made a hierarchy load script in our data:

Hierarchy(SegID, ParentID, Node, NodeParent, Node, NodePath, '|', NodeDepth)

LOAD SegID,

  ParentID,

  NodeName as Node

FROM

$(vPathName)ServiceSegmentation.xlsx

(ooxml, embedded labels, table is Sheet2);

HierarchyBelongsTo(SegID, ParentID, NodeName, TreeID, TreeName)

LOAD SegID,

  ParentID,

  NodeName

From

$(vPathName)ServiceSegmentation.xlsx

(ooxml, embedded labels, table is Sheet2);

RENAME Field Node1 to Sector;

RENAME Field Node2 to Segment;

RENAME Field Node3 to Subsegment;

This gives us a tree-viewable hierarchically ordered list.  Great, but we have a problem when I try to view this in a chart - it's double-displaying (but not genuinely doubling the numbers) and showing the intermediate hierarchy values as actual funding.  The following graph screenshot shows what I see when I view by Subsegment (lowest level) and select a segment (mid level) and all of its underlying subsegments:

Ideally, that selection would render everything except that huge wedge of "-".  Right now I have skirted the issue by using the following expression:

=sum({$<[Subsegment]-='-'>}ObligatedAmount)

But now when I click on only Segment in the "by Subsegment" graph, instead of just showing the funding broken across its respective subsegments, it just says "no data to display."

I am completely at a loss.  Any help would be greatly appreciated.

[JonnyPoole]

I'm not 100% sure where the '-' is coming from  , can you post an expanded view of the tree view list box ?

As a quick and dirty attempt,  In lieu of SET ANALYSIS to eliminate the ~ have you tried a plain old if statement  and no else clause (with 'suppress zeros' selected on the presentation tab) ?

sum( if ( Subsegment]<>'-' , ObligatedAmount) )

[#3]

Jonathan - since our segmentation is proprietary, I can't post it unaltered (i.e I could post a screenshot with everything blacked out, if you want).  The '-' is, I think, the lines in the hierarchy document where we have a Sector and Segment assigned, but no Subsegment.  I was told this was necessary to support a tree view where you could select both the lowest level or the intermediate level and have them roll up.

Regarding your workaround - trying it yields a slightly different result than my current one.  Now, instead of saying "no data to display" it just shows there is 0 value.