Dear all,
I have been struggling with this issue for quite some time now, and I cannot seem to get to the solution, despite all the helpful topics. I hope you can help me.
I have a dataset with projects. Each project has one or several deliverables that have a start and an end date. Deliverables follow each other sequentially in time. Here is an example:
Project_idDeliverable_idStartdate_deliverableEnddate_deliverable1111-1-201231-3-20121121-4-201230-6-20121131-7-201231-7-20122211-1-201215-2-201222216-2-201231-3-20123311-2-201231-7-20124411-2-201231-3-20124421-4-201215-6-201244216-6-201231-7-20124441-8-201215-9-2012
What I would like to know is how long a project takes (on average) linking it to the start date of the project. So from the example above I would like to make a line/bar chart that shows that projects starting in January take on average 151 days (mean of 212 days from project 1 and 90 from project 2) and projects starting in February take on average 204 days (mean of 181 from project 3 and 227 from project 4).
Thanks in advance for your help,
Melody
Hello Melody,
As the data does not contain an explicit Start and End date for a project but instead breaks down to the level of Deliverable ID's, can we assume that the Minimum Start Date for a Project Deliverable = Start Date and the Maximum End date for a deliverable of a project = End Date?
If so, I'd suggest a bit of custom SQL to pull the Min and Max dates for each Project.
I have pulled the data from the workbook created above and utilized a new Excel spreadsheet with the same data with a bit of custom SQL. See here:
SELECT
Max([Sheet1$].[endDate]) AS [Max endDate],
Min([Sheet1$].[StartDate]) as [Min StartDate],
[Sheet1$].[Project_id] AS [Project_id]
FROM [Sheet1$]
Group by [Project_id]
With this custom SQL, the resulting data provides the Max date and Min date for each Project and removes the field [Deliverable] from the data. Set up the resulting data with the view aggregated by Start Month and grouping each Project ID.
A calculated field provides the number of days between Min StartDate and Max endDate for each Project. This field can then be Averaged and a Table Calculation can provide the Average days to complete a Project per Start Month. Note the result is returned once per Project_id though. To manage this for the final views, we can utilize a Table Calculation function of First()==0 to filter the view to only show only the first Project_id for each StartDate Month.
I utilize Custom SQL because finding the Min and Max dates with Table Calculations in the original source table results in fields that may not be used for partitioning and grouping as desired. The simple Custom SQL creates fields that may be used and simplifies the view creation.
I'm sure others have ideas as well but this gets the job done.
-- Patrick
