2 Replies Latest reply on Feb 7, 2019 5:29 PM by Dan Cory

    Issue with Counts using Date Logic

    Pamela Lee

      Hi There!

       

      I am having difficulty with counting projects per employee based on date logic and I'm hoping someone here has successfully done something similar and can point me in the right direction.

       

      I cannot upload my actual workbook due to confidentiality issues, but I did create a spreadsheet with some dummy data that hits all of the business rules.

       

      I need to count Employee Projects by week, along with Project Logs following the same logic, in a matrix.  Here's a screenshot of one employee's Project and Log counts, just using simple calculation of IFNULL(COUNTD([ProjectName]),0) to arrive at the numbers shown - top number is Distinct Count of Projects, bottom number is Distinct Count of Logs - broken out weekly and counting Projects beginning the week of ProjectStartDate:

       

       

      My troubles begin when I attempt to apply the following logic.

       

      These are the rules by which projects and/or logs should NOT be counted based on the values of two fields, StatusHistory and StatusHistoryCreatedDate:

      • Count project weekly from ProjectStartDate until StatusHistoryCreatedDate when StatusHistory = "Project Closed"
      • If StatusHistory = "Project On Hold" and project is still on hold, then count project weekly from ProjectStartDate until StatusHistoryCreatedDate where StatusHistory = "Project On Hold"
      • If Project was on hold (StatusHistory = "Project On Hold") and then the status changed to Active (StatusHistory = "Project Active"), and then the status changed again to Closed (StatusHistory = "Project Closed")
        • Count Project weekly from ProjectStartDate until StatusHistoryCreatedDate where StatusHistory = "Project On Hold"
        • Resume counting Project weekly from StatusHistoryCreatedDate where StatusHistory is changed from "Project On Hold" to "Project Active" until StatusHistory = "Project Closed"
          • Do not count project for weeks while project was on hold or closed

       

      If anyone has any ideas as to how this might be accomplished, I will be eternally grateful.

       

      Thanks in advance,

       

      Pamela