Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
A simple sum(hours/[Hours Full day]) should do the trick if you have this field defined.
Try as expression in your chart something like
=sum(aggr(sum(hours) / [hours full day], project, name))
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
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.
I think Marc is right, his expression should be enough.