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

# Date and time connection

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

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

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