Some background -
For our new hire process, we have a master request generated (REQ) which then kicks off several individual task tickets for various areas (account setup, hardware deployment, maybe some specific software licensing if requested). Depending on what items are requested by the manager, the # of these individual tasks may vary, but will always refer back to a master original request # (REQ).
Below is a sample of how this may look:
|Item||Task||Short Description||Created Date||Completed Date||Due Date|
|REQ1||Null||New Hire: John Doe - 12/1/2016||11/1/16||11/27/16||12/1/16|
|REQ1||TASK123||User Manager Survey||11/1/16||11/5/16||12/1/16|
|REQ1||TASK124||User Deployment Setup - John Doe||11/5/16||11/27/16||12/1/16|
|REQ1||TASK125||User Account Setup - John Doe||11/5/16||11/18/16||12/1/16|
|REQ2||Null||New Hire: Jane Doe 12/2/2016||11/1/16||12/1/16||12/2/16|
|REQ2||TASK234||User Manager Survey||11/1/16||11/3/16||12/2/16|
|REQ2||TASK235||User Deployment Setup - Jane Doe||11/3/16||11/7/16||12/2/16|
|REQ2||TASK236||User Account Setup - Jane Doe||11/3/16||11/13/16||12/2/16|
|REQ2||TASK237||User Salesforce Setup - Jane Doe||11/3/16||12/1/16||12/2/16|
|REQ3||Null||New Hire: John Smith 12/2/2016||11/15/16||11/28/16||12/2/16|
|REQ3||TASK345||User Manager Survey||11/15/16||11/27/16||12/2/16|
|REQ3||TASK346||User Deployment Setup - John Smith||11/17/16||11/23/16||12/2/16|
|REQ3||TASK347||User Account Setup - John Smith||11/17/16||11/18/16||12/2/16|
|REQ3||TASK348||User MSDN Setup - John Smith||11/27/16||11/28/16||12/2/16|
"Created Date" ='s the date the REQ or specific individual TASK was created on
"Completed Date" ='s the date where the specific individual TASK was completed on AND/OR the date the last remaining TASK was completed on (so all tasks completed), which then resolves the original request automatically on the same date
"Due Date" ='s Start date of the new hire
There are various if/then workflows on the back end not listed but for this case they are irrelevant to this Q.
What I'm looking to do is create some kind of visual representation (I'm thinking a lollipop chart, but am open to better ways to represent the data) where by we can see:
(1)AVG REQ Created to Due Date
(2) AVG REQ Created to Completed Date (so last/all task completed)
(3) AVG TASK(s) Created to Completed Date (could be multiple marks, depending on # of tasks requested)
- Note, we'll use Short description 'contains' to separate; so one may be include ("contains") 'Account'; 'Deployment', 'Survey', so have (x3)TASKS / (1) REQ, and one (where software was requested) may included 'Account', Deployment', Survey', 'MSDN', so have (x4) TASKS/ (1) REQ
The goal is show how long (or short) manager's are submitting their tickets in advance, how long individual business units are taking to do their complete their tasks (ex, accounts are always fast but we always seem to be waiting on hardware?), and where in relation to the new hire's start date are all tasks being completed.
Any idea on how to best represent the above?