Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I need some help consolidating some data that is being loaded from an excel spreadsheet.
The spreadsheet contains peoples time logging activitives.
an example of the spreadsheet looks like:
Name | Date | Task Name | Hours |
---|---|---|---|
JP | 11/07/2012 | Process Project 1 | 6 |
JP | 11/07/2012 | Process Project 2 | 1 |
JP | 11/07/2012 | OOO | 1 |
JP | 12/07/2012 | Process Project 1 | 4 |
JP | 12/07/2012 | Process Project 2 | 3 |
JP | 12/072012 | Admin | 2 |
fields in the script are:
Name
Date
Task
TimeSpent
The issue I need to solve is to be able to draw a graph of FTE equivalent for each project.
So the formula should be for any specific task on any day: time spent on task / total time logged for the day, which will give me the portion of time someone spent on the particular task for the day.
So I was thinking I would create a new table linked on name and date that looks like below
Name
Date
TotalTimeLoggedForDay
Name | Date | TotalTimeLogged |
---|---|---|
JP | 11/07/2012 | 8 |
JP | 12/07/2012 | 9 |
If anyone can explain how I would create the new table from the existing table to produce a result as described above that would be much appreciated.
Thanks
JP
Hi,
To get that table, assuming the field names you have provided in the post, it should be something like the following:
FTE:
LOAD Name,
Date,
Sum(TimeSpent) AS TotalTimeLoggedForDay
RESIDENT TableWithTasks
GROUP BY Name, Date;
If you can replace the RESIDENT by a FROM qvd file the result will be quite faster.
Hope that helps.
Miguel
Why do you need a seperate table? You need to do this during reload? A chart can easily be created with what you want.
Hi,
To get that table, assuming the field names you have provided in the post, it should be something like the following:
FTE:
LOAD Name,
Date,
Sum(TimeSpent) AS TotalTimeLoggedForDay
RESIDENT TableWithTasks
GROUP BY Name, Date;
If you can replace the RESIDENT by a FROM qvd file the result will be quite faster.
Hope that helps.
Miguel
Hi Ioannis
Thanks for the speedy reply. I figured it could be done in a chart but couldn't figure out what the formula should be, because as you make selections on the date or task the hours logged will be available but I couldn't figure out how to get the total hours.
Any suggested formulas?
Thanks
Jp
Hi Miguel
Thanks that is super.
Cheers
Jp