Discussion Board for collaboration related to Creating Analytics for QlikView.
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.
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
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.
Go to 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:
and divide first by second
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,
Thanks man. Brilliant!
I can now refine it further and replace the sums inside the aggr by some sums with set analysis.