4 Replies Latest reply: Oct 2, 2017 1:03 PM by Matt McIlvride RSS

    Count all datetime instances that occur on Monday?

    Matt McIlvride

      I am working with a dataset that looks something like this:

       

      CompletionDatetimeCompletionDateEmployeeCondition 1Condition 2
      2017-09-25 10:32:48.1842017-09-25Employee1Yes

      No

      2017-09-26 09:22:17.4472017-09-26Employee2YesNo
      2017-09-28 12:08:54.1062017-09-28Employee3NoNo
      2017-10-02 09:28:23.8572017-10-02Employee4YesYes

       

      I can count the number of completed tasks by counting the number of values in the CompletionDatetime column (the example table above has 4). I need to create a report that aggregates CompletionDatetime by weekday for the past 90 days. So, if CompletionDatetime has a value, it should be counted as 1 for that weekday. The final result would look like this:

       

      EmployeeMondayTuesdayWednesdayThursdayFriday
      Employee1157208194177201
      Employee210621018420098
      Employee32770101134137
      Employee402122301170

       

      I have figured out how to count only the values in the past 90 days:

       

      Count( {$<CompletionDate = {">=$(=Date(Today() - 90))<=Today()"}>} CompletionDatetime)

       

      But I am stumped as to how to add a weekday condition to it.