Aggregation challenge

[wdonckers]

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

[swuehl · Accepted solution]

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

[wdonckers]

Thanks man. Brilliant!

I can now refine it further and replace the sums inside the aggr by some sums with set analysis.