topic Re: Aggregation challenge in QlikView

Aggregation challenge

wdonckers — Fri, 07 Dec 2012 15:57:21 GMT

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

swuehl — Fri, 07 Dec 2012 16:45:39 GMT

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

Re: Aggregation challenge

wdonckers — Mon, 10 Dec 2012 09:19:17 GMT

Thanks man. Brilliant!

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