Expression Help

[#1]

I have an expression:

SUM( {1} if(OrderBTL >= 2, if(OrderBTR <= 9, Amount)))

that gives me the exact result that I want, except I would like to replace the constants (2 & 9) with fields from the currently selected set. For 2 I would like to use the field called [left] and for 9 I would like to use the field called

. I have tried a number of different things but have been unable to get it right.

I realize that this expression will only be evaluated once for the sheet,

I am open to suggestions for improvement as well.

Thanks in advance

John

[JonnyPoole]

Maybe this is closer:

-----------

PeopleTemp:

HierarchyBelongsTo(PersonKey, ParentKey, Name, AncestorID, AncestorName, DepthDiff)  LOAD * INLINE [

  PersonKey,ParentKey, Name, ChildBranch

    1,,'Bobby'

    2,1,'Bill','Left'

    3,1,'Peter','Right'

    4,2,'Kate','Left'

    5,2,'Lloyd','Right'

    6,4,'John','Left'

    7,3,'Gino','Left'

];

// PersonKey,ParentKey, Name, Branch

People:

Load * , if(PersonKey=AncestorID,'Current Node','Children') as AggregationType

resident PeopleTemp;

drop table PeopleTemp;

Orders:

LOAD * INLINE [

  OrderNum, PersonKey, Amount, OrderLeft, OrderRight

    1,6,100,4,5

    2,6,100,4,5

    3,6,100,4,5

    4,7,500,11,12

    5,3,250,12,13

    6,4,150,3,6

];

--------------------

[JonnyPoole]

Was this of any help  ?  I liked the post because it brought an angle that was new to me with respect to hierarchies, that is, a hierarchy with just 2 branches (left and right) at each node and how best to reflect that attribute in the hierarchy load.  I did feel the expression and load script i presented resolved the situation but the one piece that would need to be customized is to transform the data into a format that i used in my hierarchybelongsto load.  anyways , it was interesting so if you get back to this i'd love to hear how things turned out.

[#4]

I haven’t had a chance to try it out yet, but I will in the next day or so.

I’m glad I was able to challenge you!

[#5]

Jonathan,

your solution appears to work but for only 1 level down in the tree.

For example, if I choose "Bobby" and "Right" it shows 250, but it should show 750 (Peter's 250 + Gino's 500).

I believe the example works for "Bill" and "Left" because both Kate and John are on the left side. If we were to move John to Kate's right side I bet it will not work.

Thanks again for the attempt. Your approach has given me something else to think about as I work towards a solution.

[JonnyPoole]

John - i see exaclty what you are referring to.  The left/right branch field propogates to all children giving you not only the left branch children but the left branch of those children too (grandchildren) which is wrong for this need.

I'm thinking another attribute is needed to differentiate children vs grandchildren.  I'll take another crack at this when i have some cycles.