1 of 1 people found this helpful
When I look at the TDE, it doesn't contain the Intrvl Start Tm, Call Cnct Tm or Hld Tm fields, so it isn't very helpful for troubleshooting this question.
However, from looking at the screenshot you shared, I do have a thought.
Are those three fields possibly times without associated dates?
Tableau, like many other programs, uses 12/30/1899 as the "base date" and adds it to times without dates.
Yes that is correct the Trans Dt is the individual associate shift date and all other fields are times in hh:mm:ss for calls answered, hold time, etc..
I wrote an initial SQL statement:
SELECT AVAYA_LOGIN_ID, sum(cast(substring(CALL_CNCT_TM from 1 for 2) as integer)*60.00+cast(substring(CALL_CNCT_TM from 4 for 2) as integer)+cast(substring(CALL_CNCT_TM from 7 for 2) as integer)/60.00) as CALL_CNCT_TM_MINS but for some reason its doing some weird the numbers are way to large. Also I can't attach the file as its too large.
Got it. In order to use this as a proper date/time I think you will need to combine the Trans Dt with the time field then make a date/time out of that, as Tableau doesn't recognise just time alone.
Okay, correct me if I am wrong. So first in the initial query, just combine all the Time Fields with the Date field then join the Query and the Table on the unique ID field. Finally hide all the old (wrong format) fields from the table.
That should work?
I think you might need to do it after the fact because each time field would need to be separately combined with the date field. My SQL skills are not enough to know whether you can do this in the initial query.