2 Replies Latest reply on Dec 16, 2016 8:38 AM by Shinichiro Murakami

    Relational Date Lollipop Chart?

    Adam Rauh

      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:



      ItemTaskShort DescriptionCreated DateCompleted DateDue Date
      REQ1NullNew Hire: John Doe - 12/1/201611/1/1611/27/1612/1/16
      REQ1TASK123User Manager Survey11/1/1611/5/1612/1/16
      REQ1TASK124User Deployment Setup - John Doe11/5/1611/27/1612/1/16
      REQ1TASK125User Account Setup - John Doe11/5/1611/18/1612/1/16
      REQ2NullNew Hire: Jane Doe 12/2/201611/1/1612/1/1612/2/16
      REQ2TASK234User Manager Survey11/1/1611/3/1612/2/16
      REQ2TASK235User Deployment Setup - Jane Doe11/3/1611/7/1612/2/16
      REQ2TASK236User Account Setup - Jane Doe11/3/1611/13/1612/2/16
      REQ2TASK237User Salesforce Setup - Jane Doe11/3/1612/1/1612/2/16
      REQ3NullNew Hire: John Smith 12/2/201611/15/1611/28/1612/2/16
      REQ3TASK345User Manager Survey11/15/1611/27/1612/2/16
      REQ3TASK346User Deployment Setup - John Smith11/17/1611/23/1612/2/16
      REQ3TASK347User Account Setup - John Smith11/17/1611/18/1612/2/16
      REQ3TASK348User MSDN Setup - John Smith11/27/1611/28/1612/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?



        • 1. Re: Relational Date Lollipop Chart?

          Hey Adam,


          You'll be more likely to get a helpful response if you throw this into a packaged workbook and make it easier for people to manipulate the data. Perhaps Yuri Fal or Shinichiro Murakami could then help out.



          • 2. Re: Relational Date Lollipop Chart?
            Shinichiro Murakami



            Regardless of what kind of analysis you will do, starting point is pivoting data.




            Rename fileds


            [Description Category]

            if contains([Short Description],"New Hire" )

            then "New Hire"

            elseif contains([Short Description],"User Account Setup" )

            then "User Account Setup"

            elseif contains([Short Description],"User Deployment Setup" )

            then "User Deployment Setup"

            elseif contains([Short Description],"User Manager Survey" )

            then "User Manager Survey"



            [Start Date]

            if [Date Category]="Created Date" then [Date] end


            [Start to Complete]

            {fixed [Item],[Task]:max(if [Date Category]="Completed Date" then [Date] end)}-

            {fixed[Item], [Task]:max(if [Date Category]="Created Date" then [Date] end)}








            1 of 1 people found this helpful