2 Replies Latest reply on Aug 7, 2018 6:34 AM by Serhii Ivashkovych

    Date and time connection

    Serhii Ivashkovych

      Hello! I`m new for Tableau, so I have one problem.

      I have one table with  information about number of abandoned calls during one working day ( sheet 1). In the second table I have the name of the Agent and START and END time of their shift (Sheet 3).

       

      I need to show in the same chart how many agents was working during specific time, and how much calls were abandoned.

       

      How can I creat field that will show how many persons were working at that moment, if I have only information about their stars and end working hours. Should I use some SQL script for this?

      Thank you)

        • 1. Re: Date and time connection
          Mahfooj Khan

          Hi,

           

          Connect your file in tableau and join sheet 1 with sheet 3

          For joining both source you need to create a joining condition in sheet 1

          Use below calculation to fetch the hours:minutes AMPM from sheet 1 date and time field to join with Start field from Sheet 3

           

          IF DATEPART('hour',[Date and Time]) = 0 THEN '12'

          ELSEIF DATEPART('hour',[Date and Time])<10 THEN STR(DATEPART('hour',[Date and Time]))

          ELSEIF DATEPART('hour',[Date and Time])>12 THEN STR(DATEPART('hour',[Date and Time])-12)

          ELSE STR(DATEPART('hour',[Date and Time])) END //hour

          + ':' +

          IF DATEPART('minute',[Date and Time])<10 THEN '0'+STR(DATEPART('minute',[Date and Time]))

          ELSE STR(DATEPART('minute',[Date and Time])) END //minute

          +

          IF DATEPART('hour',[Date and Time])>=12 THEN 'PM' ELSE 'AM' END //AM or PM.

           

           

          Once done then go to sheet and create a calculated field called HH:MM AMPM using the above logic again. Use this field in canvas

          workbook attached for your reference. let us know if this help.

           

          Mahfooj

          • 2. Re: Date and time connection
            Serhii Ivashkovych

            Thank you, but how can I increase the time range because I need the information about hole day?