2 Replies Latest reply on Aug 14, 2015 7:56 AM by pooja.gandhi

    working with time data

    nagesh koppolu

      tableau will not allow us to subtract string to string. That is ok but how to find two times difference for example  login time is 6:00:00 am logout time is 2:30:00 pm. My queary is  If an employee comes late more them two time in a month he will be charged half day salary fine. I tried as possible as I can. I am very week in Time calculations data. Will any one help me to find the query, find below attachment.

        • 1. Re: working with time data
          Rody Zakovich

          Hello Nagesh,

           

          Here is what I am thinking....

           

          First, create a calculated field where you Convert Actual Login to DATETIME. We are going to use this in another calc, and converting it makes it easier to work with. I made the assumption that Actual Login is the time they are expected to arrive.

           

          Actual Login Date Time =>

           

          DATETIME([Actual Login])

           

          Next Create a calc that determines if the Employee was late more than 2 in a given month

           

          Here is the Table Calc version. I could use an LoD, but it appears you have left out Employee data for confidentiality.

           

          IF WINDOW_SUM(SUM(IF DATEPART('hour', [First In]) >= DATEPART('hour', [Actual Login DateTime])

          AND DATEPART('minute', [First In]) > DATEPART('minute', [Actual Login DateTime])

              THEN 1

          ELSEIF ISNULL([First In])

              THEN 0

          ELSE 0

              END)) >= 2 THEN 'Deduct Pay' ELSE 'Good Attendance' END

           

          You may need to play around with how this is being computed (Table Down/Pane down/etc) depending on your Viz Level of Detail. But this should get you started.

           

          Let me know if this is on the right track, or if this is different that what you needed.

           

          Regards,

          Rody

          • 2. Re: working with time data
            pooja.gandhi

            Hi Nagesh,

             

            I approached this problem a little differently. I assume when you say subtract 2 strings, you are trying to see how many hours an employee worked on a specific day? And I feel like, when you say you want to bucket them categorically if they came in late twice in a month. Meaning if they logged on late, correct? I am not sure how you are determining that part though, because the login times are very different for different days in a month. A good example is February 2015. Some days they logged on at 9:30 PM, some days, 12 PM and some days 6 AM. So I am not sure how you are determining if someone came in late more than twice a month unless there is another assumption that you did not mention here.

             

            That said, I made the strings of date datatype by doing exactly what Rody suggested.

             

            Login Modified - DATETIME([Actual Login]) and another one for

            Logout Modified - DATETIME([Actual L Logout])


            You will see that Tableau defaults the date to 1/1/1900. Provided that the data source string input of login times and logout times are correct, I created a formula to add 1 day to the logout datetime if login datetime is > than the logout datetime. I did this because if the login time is 1/1/1900 9:30 PM and logout time is 1/1/1900 6:00 AM Tableau will give a difference of 15 hours because these times are on the same day. To avoid that issue, the formula:


            Dateadd - Logout Time  - IF datepart('hour', [Login Modified]) > datepart('hour', [Logout Modified]) then dateadd('day', 1, [Logout Modified]) else [Logout Modified] end


            will add another day to the logout time to accurately get the difference in hours. So the logout time for 1/1/1900 6:00:00 AM becomes 1/2/1900 6:00:00 AM


            Then you create the datediff formula between Dateadd - Logout Time and Login Modified


            Datediff - datediff('hour',  [Login Modified], [Dateadd - Logout Time])


            Capture.PNG