2 Replies Latest reply: Dec 10, 2012 4:19 AM by Wim Donckers RSS

    Aggregation challenge

    Wim Donckers

      Hi,

      I'm trying to do the following:

      revenue is on level of project and not on resource. Hrs are on project and resource level. Team is derived from resource.

       

       

      Data:

       

      project   resource  team hrs  revenu

      A                        0    6000

      A         1    teamX     20   0

      A         2    TeamY     30   0

      A         3    TeamY     10   0

      B         -    -         0    15000

      B         1    teamX     100  0   

      B         4    TeamX     100  0

      B         2    TeamY     50   0

       

      Goal is to report revenue by team by applying the average rate (revenue/hrs) on project level to the resource hours

       

      Result should be:

       

      Team            rate hrs  revenu

      X               63,6 220  14000

      Y               77,8 90   7000

      total           67,7 310  21000

       

      the average rate of project A = 6000/(20+30+10)=100

      the average rate of project B = 15000/(100+100+50)=60

       

      logic:

      14000 = 20*100+100*60+100*60

      7000 = 30*100+ 10*100 + 50*60

       

      I've been trying with aggregations and sets but to no avail.

      Desperately need some help here.

      thanks,

      Wim

        • Re: Aggregation challenge
          Stefan Wühl

          I think you can calculate your revenues per team (e.g. as expression in a straight table with dimension team) like this:

           

          =sum(aggr(sum(total<project> revenu) / sum(total<project> hrs)*sum(hrs),project,team))

           

          Then calculate the hrs per team:

          =sum(hrs)

           

          and divide first by second

           

          =column(1)/column(2)

           

          Is this what you are looking for? I assumed a front end only solution, if you are not dependent on selections, you can calculate your numbers also the script.

           

          Hope this helps,

          Stefan