Forum Discussion
Measure from two tables
If this helps: Here is some sample data as an example
Task Table:
| Task | Project | Window |
| a | p1 | 12 |
| b | p1 | 20 |
| c | p1 | 15 |
| d | p2 | 35 |
| e | p2 | 42 |
| f | p3 | 18 |
| g | p3 | 8 |
| h | p3 | 23 |
| i | p3 | 21 |
| j | p4 | 14
|
People table
| Person | Task | LastDateWorked |
| 1 | a | 5/9/2020 |
| 1 | b | 4/24/2020 |
| 1 | c | 5/3/2020 |
| 1 | d | 5/9/2020 |
| 1 | e | 5/7/2020 |
| 2 | f | 4/24/2020 |
| 2 | g | 5/4/2020 |
| 2 | h | 4/29/2020 |
| 2 | i | 5/18/2020 |
| 2 | j | 5/19/2020 |
| 3 | a | 5/4/2020 |
| 3 | b | 5/9/2020 |
| 4 | c | 5/9/2020 |
| 4 | d | 4/21/2020 |
| 4 | e | 5/18/2020 |
| 4 | f | 5/10/2020 |
| 4 | g | 5/7/2020 |
| 5 | h | 4/21/2020 |
| 5 | i | 5/5/2020 |
| 5 | j | 5/11/2020 |
So for task "A" with a 12 day window. Using 5/18/2020 as today, 12 days ago is 5/6. Person 1 worked 5/9 so he/she is inside the window (good) while person 3 is outside the window (Bad). Two people work, one good, so 50% for that task.
I would use the same logic when doing it by project, or doing it by person.
Ashish,
Thanks. It's a great start I think. It doesn't report task g as 0% and when I look at it by project rather than task it doesn't calculate correctly. However, I'm hoping this gives me enough of an idea to do what I need. I'll let you know.
This works for the individual tasks, but not for projects. Three of the four have the same percentages for each task (projects P1, P2, and P4) so the project level numbers are correct I think only because of this. But project P3 is wrong. There are 8 people who have reported time (yes some are the same person in different tasks but that doesn't matter) and 4 have time within the window. Project p3's percent should be 50%.
In total, 20 people (again the fact it was the same person in different tasks is not important) reported time and in 13 cases they were in the window, so 65%.
Hopefully this better explains it all. I do very much appreciate your working on this!!!