6 Replies Latest reply on Oct 8, 2018 5:02 AM by Moses Dhas

    Get distinct count based on date criteria

    Moses Dhas

      Hi,

       

      i am trying to get a distinct count of WorkPack records based on a date criteria

       

      WorkPackDisciplinePlan Early StartPlan Early Finish
      A001Structural2-Oct-1815-Oct-18
      A001Structural24-Sep-186-Oct-18
      A002Piping10-Oct-1815-Oct-18
      Mechanical10-Oct-1818-Oct-18

       

      1. Criteria 1 - Distinct count of WorkPack by Discipline (can have other attributes later) where Plan start <= 3-Oct-18
        1. Result 1 - New Field (Total WorkPack Count) Structural = 2, Piping = 1, Mechanical = 0
        2. Result 1 - New Field (TBA WorkPack) Mechanical = 1 because WorkPack is blank
      2. Criteria 2 - Distinct count of WorkPack by Discipline (can have other attributes later) where Plan start <= 3-Oct-18 and Plan Start >= (3-Oct-18) - 6
        1. Result 2 - New Field (Weekly WorkPack Count) Structural = 1, Piping = 0, Mechanical = 0
        2. Result 2 - New Field (TBA WorkPack) Mechanical = 0 because WorkPack is blank

       

      i tried the below formula, i have created a date parameter to capture the 3-Oct-18 which is [cutoff]

       

      {FIXED [WorkPack] : COUNTD(IF [PLAN Early  START] <= [Cutoff] THEN [WorkPack] END )}

       

      am getting an error "Cannot compare date and string values"

       

      any help would be much appreciated

       

      thanks,