11 Replies Latest reply: Jun 12, 2013 5:35 AM by Eldad Tsipris RSS

    Multti level hierarchy and aggregation

    Eldad Tsipris

      Hi,

       

      I have Table with the following information:

       

      Accounts:

      AccountID , Name , ParentID , Weight

      1     ,a     ,4     ,-1

      2     ,b     ,4     ,1

      3     ,c     ,4     ,1

      4     ,d     ,5     ,1

      5     ,e     ,5     ,

      6     ,f     ,2     ,-1

      7,    ,g    ,2     ,1

       

      Data:

      Account, Value

      1,     10

      1,     5

      2,     20

      3,     30

      4,     40

      6,     60

      7,     70

       

      The "Weight" field says in which way the child should sum up to its father.

      I tried every combination of hierarchy/ belongsto but I could not get the correct result when using "Weight".

       

      The correct result should be

      Account, Total

      1     10+5 = 15

      2     20 + (-60+70) = 30

      3     30

      4     40+(-15+30+30) =85

      5     85

      6     60

      7     70

       

      In short - while summing up each account, it should concider its childrens and grand childrens (etc...)  weight.

      I have attached a QV screenshot of what I am trying to achieve

       

      is it possible to do it it in QV?