topic aggr() ? in QlikView

aggr() ?

Mon, 26 Mar 2012 14:34:59 GMT

hi all,

i have the following situation: my db contains people. each person is suppose to work x hours per day. people work on different projects. internal and external projects.

example:

people table:

name	hours full day
John Smith	8
Richard Oaks	4

project table

week	project	project type	name	hours
13	project A	internal	John Smith	40
13	project A	internal	Richard Oaks	9,5
14	project A	internal	John Smith	24
15	project B	external	John Smith	32
15	project B	external	Richard Oaks	12

Now, e.g. on a bar chart with project type as dimension i can easily show the hours spent on project types by sum(hours).

BUT: When i want to (on the same chart) show the days spent over project types - how would i do that ? What would the expression look like ? For John Smith a whole day consists of 8 hours. For Richard Oaks it consists of 4 hours. (Means: If john Smith spends 40 hours, he worked 5 full days. If Richard Oaks spends 40 hours, he worked 10 full days.)

Hope my problem is clear.

Thanks for some advise!

aggr() ?

Mon, 26 Mar 2012 14:37:54 GMT

A simple sum(hours/[Hours Full day]) should do the trick if you have this field defined.

aggr() ?

swuehl — Mon, 26 Mar 2012 14:45:43 GMT

Try as expression in your chart something like

=sum(aggr(sum(hours) / [hours full day], project, name))

aggr() ?

Mon, 26 Mar 2012 14:46:16 GMT

No, because the correct result - for overall days worked (internal & external) would be:

John 12 days

Richard 5,3 days

----------------------

17,3 days

But the expression you suggested would lead to:

sum(hours/hours full day)

= (117,5 / 12) = 9,9

aggr() ?

Mon, 26 Mar 2012 14:51:00 GMT

My suggestion should work correctly if Hours per day is defined in the script and linked to the employee. It would not take the total sum of hours divide by the total sum of hours full day, but before summarizing each it would split the hours and divide by the employee that is linked full day hours.

If it is not defined in the script then you would need an if statement to pair the correct full day hours to each employee.

aggr() ?

swuehl — Mon, 26 Mar 2012 14:52:15 GMT

I think Marc is right, his expression should be enough.