8 Replies Latest reply on May 10, 2018 12:44 PM by Shinichiro Murakami

    Need help with filterting parent projects and milestone projects

    Sonnie Chen

      I have access to a database which has details of projects submitted by users which are assigned to different vendors.


      I am trying to calculate average time taken by vendors for projects (Avg time for Submit to Quote Approved, Quote Approved to Completed etc)

      There are two types of projects, Ad hoc projects and Milestone projects and I need to build separate views for them.


      Adhoc projects are individual standalone projects. Most of our projects are Adhoc.


      For milestone projects, there will be a parent project and one or more sub projects (child projects)


      As shown in the sample table below, MM1234 and SP1234 are milestone projects. These projects have child projects which have their respective Parent Request No updated.


      Is there a way I can filter for only these milestone projects ( MM1234 and SP1234) so that I can calculate their average - The child projects of these projects should not be included in the average calculation


      Also attached the sample data for reference       


      Project idProj TitleClientSubmit DateQuote Approved
      Completed DateStatusVendorParent Request
      DD1234Slides creationxy1/1/20181/1/20181/2/2018CompletedNorth
      TR1234Translation jobBC2/2/20182/3/20182/7/2018CompletedEast
      DD1234Slides creation 2xy1/3/20181/3/20181/7/2018CompletedNorth
      MM1234Media monitoring for
      BC1/1/20181/1/2018 Quote ApprovedWest
      MM1235Media monitoring for
        2018 -Jan
      MM1236Media monitoring for 2018 - FebBC1/1/20181/1/20183/30/2018CompletedWestMM1234
      MM1237Media monitoring for
        2018 - Mar
      BC1/1/20181/1/2018 Quote ApprovedWestMM1234
      MM1238Media monitoring for
        2018 - Apr
      BC1/1/20181/1/2018 Quote ApprovedWestMM1234
      DD1235Logo Creationxy3/3/20183/3/20183/5/2018CompletedNorth
      TR1235Translation job 2BC4/10/20184/10/2018 Quote ApprovedEast
      SP1234SharePointamc2/15/20182/20/2018 Quote ApprovedSouth
      SP1235SP Designamc2/15/20182/20/20182/1/2029CompletedSouthSP1234
      SP1237SP Testingamc2/15/20182/20/2018 Quote ApprovedSouthSP1234