2 Replies Latest reply on Feb 13, 2019 2:47 PM by Aileen Rotondo

    Assign numeric values to Weekdays

    Aileen Rotondo

      hi,

       

      I am trying to keep track of how many people have their hours in at full capacity.

       

      40 hours a week is acceptable for a whole work week. Any less is "Bad".

      Monday through thursday the workhours per day is 8.75, and on Friday it's 5 hours.

       

      For each month I'd like to know whether the hours were met. Sometimes the month starts at the end/beginning of a week so I need to have each weekday have a particular value so that if the week is cut off then I capture those hours.

      In the example below it is February, for week 5 most people have 5 hours because the only day of that week in Feb was Friday(feb 1st) and since it was a friday the value of 5 would meet my standard. So anything with 5 or above should be "Good"

       

      I was hoping that if I colored each week by "Good" and "Bad" that it would differentiate which weeks had hours met and which didn't. But I don't know why my results are crazy.

       

      (you can see my calculations below)

      HoursMet.png

        • 1. Re: Assign numeric values to Weekdays
          Jennifer VonHagel

          Hi Aileen, it's difficult to troubleshoot this without having access to your workbook, though I understand it can't be shared for confidentiality reasons.

           

          I created a tiny sample data set and materially re-created your logic. Any which way I slice and dice my data (by day, week, month, by user or across users) the sums come out as expected.  I've added the syntax for my calculations below, though logically they do what yours do. When replicated, your logic works; maybe something's up with your underlying data?

           

          In cases like these, I rebuild the view from scratch, adding an element at a time at the lowest level of detail. So maybe start a fresh sheet where you put the exact date, person, sum(hours), sum(expectation). I assume this is the lowest level of detail. Then start adding in components (other filters, rolling up to the week instead of looking at each date, etc) and see where things go awry.

           

          My calcs:

           

          Weekday: DATENAME('weekday',[Date])

           

          Expectation:

          IF [Weekday] = 'Saturday' OR [Weekday] = 'Sunday'

          THEN 0

          ELSEIF [Weekday] = 'Friday'

          THEN 5

          ELSE 8.75

          END

           

          Color:

          IF sum([Hours]) >= SUM([Expectation])

          THEN 'Good'

          ELSE 'Bad'

          END

           

          Hope this helps,

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: Assign numeric values to Weekdays
            Aileen Rotondo

            Thank you for your response,

             

            I exported my data to a CSV and realized it would duplicate records whenever someone had entered time on different projects.

             

            I changed my Week expectation to be aggregated as MIN instead of SUM and that solved my issue.

             

            Thank you