4 Replies Latest reply on Aug 26, 2016 10:25 AM by Simon Runc

    Late Punch Count

    elizabeth.gancas

      Hi Tableau Community,

       

      I had this question a few months ago and need additional help - My objective is to find the number of days an employee punches in after a value in a parameter.

       

      I was advised to use this calculation to determine the number of times an employee punched in after the value in the parameter.

       

      IF

      DATEDIFF('minute'

      ,[In Punch]

      ,DATETIME(STR(DATE([In Punch]))+' '+STR([Punch In Hour Limit])+':'+STR([Punch In Minute Limit]))

      ) < 0 THEN 'Late'

      ELSE 'Early'

      END

       

      My issues is: When I replace the 'In punch' values with 'Out Punch' my results show that all punch outs are late.

       

      IF

      DATEDIFF('minute'

      ,[Out Punch]

      ,DATETIME(STR(DATE([Out Punch]))+' '+STR([Punch Out Hour Limit])+':'+STR([Punch Out Minute Limit]))

      ) < 0 THEN 'Late'

      ELSE 'Early'

      END

       

      Out Punch is a date time dimension

      Punch Out Hour Limit/Minute Limit are integer parameters

       

      Please let me know if you have any ideas as to what I'm doing wrong.

      Thank you!

      Elizabeth

        • 1. Re: Late Punch Count
          Simon Runc

          hi Elizabeth,

           

          I've re-built your formula, so I can see what it was doing (and used the nifty drag and drop to check what each part is doing)...but it is just that you need the parameter hour in 24 hour format

           

          When I set the time to 16 (so not 4) and 40...it shows me those who punched in before and after 16:40 fine.

           

          ...this seems too simple to be the solution, so if you let me know what behavior you are expecting I'm happy to take a look.

          • 2. Re: Late Punch Count
            chris.moore.11

            I think your parameter is assuming a.m.

             

            So when you put 4:30 it thinks the morning.

             

            Try adding 12 to the hour limit...

             

            IF

            DATEDIFF('minute'

            ,[Out Punch]

            ,DATETIME(STR(DATE([Out Punch]))+' '+STR([Punch Out Hour Limit] + 12)+':'+STR([Punch Out Minute Limit]))

            ) < 0 THEN 'Late'

            ELSE 'Early'

            END

            • 3. Re: Late Punch Count
              elizabeth.gancas

              Oh wow! I'm embarrassed I didn't catch on to that. Thank you both for your help

              • 4. Re: Late Punch Count
                Simon Runc

                No problem...sometime you are just too close to a problem!! (done it myself many times!)...and you'd done the really complicated bit (breaking out and rebuilding the date/time from parameter) so well...so often the way!