12 Replies Latest reply on May 4, 2016 11:50 PM by Rajeshwari Guddeti

# Time difference is displayed as negative

Hello everyone,

I have two dates, Start time and End time having Date&Time format.. I need to plot how many no.of alert_id's are generated in a given time range (I.e, between start time and end time).. I have used below calculation to get the time difference in seconds..

(DATEPART('hour',DATETIME([End Time]))*3600+

DATEPART('minute',DATETIME([End Time]))*60+

DATEPART('second',DATETIME([End Time]))) -

(DATEPART('hour',DATETIME([Start Time]))*3600+

DATEPART('minute',DATETIME([Start Time]))*60+

DATEPART('second',DATETIME([Start Time])))

Now, so as to convert the time difference from seconds to HH:MM:SS format, I have used below calculation

STR(INT(SUM([Convert to Seconds])/3600))+ ":" +

IF LEN(STR(INT((SUM([Convert to Seconds])%3600)/60)))= 1 THEN "0" + STR(INT((SUM([Convert to Seconds])%3600)/60))

ELSE STR(INT((SUM([Convert to Seconds])%3600)/60)) END + ":" +

IF LEN(STR(INT(SUM([Convert to Seconds])%60)))= 1 THEN "0" + STR(INT(SUM([Convert to Seconds])%60))

ELSE STR(INT(SUM([Convert to Seconds])%60))

END

But when i implement the same calculation I'm getting time in negative difference as given below....

Can someone help me in getting proper time difference and please help me with any ideas on how to plot the graph for providing visualizations which shows no.of alert values generated against given time range

• ###### 1. Re: Time difference is displayed as negative

Please someone help me with the solution...

1 of 1 people found this helpful
• ###### 2. Re: Time difference is displayed as negative

Hi Rajeshwari,

If your start and end dates are datetime fields then why you're converting them to datetime then extracting datepart from that. Just asking

You can simply do this. Lets assume ST=Start Time and ET=End Time

Get the timediff in seconds from both the dates. Use this calc field to convert in HH:MM:SS format.

I hope this may help you. Let me know if you've any query.

Mahfooj

2 of 2 people found this helpful
• ###### 3. Re: Time difference is displayed as negative

Thanks Mahfooj..

But thats also not working.. its giving me the same negative field.. when create a calculated field to get HH:MM:SS format.. it automatically goes into measure...  I'm still getting negative values..

1 of 1 people found this helpful
• ###### 4. Re: Time difference is displayed as negative

Rajeshwari,

Don't forget to mark my answer as correct and try to close the thread if you got your desired output from my answer.

Mahfooj

1 of 1 people found this helpful
• ###### 5. Re: Time difference is displayed as negative

But thats also not working.. its giving me the same negative field.. when create a calculated field to get HH:MM:SS format.. it automatically goes into measure...  I'm still getting negative values..

1 of 1 people found this helpful
• ###### 6. Re: Time difference is displayed as negative

Can you share your sample workbook in .twbx format?

• ###### 7. Re: Time difference is displayed as negative

Just go through the attached workbook for your reference.

version 9.2

• ###### 8. Re: Time difference is displayed as negative

Hi Mahfooj,

I cannot share the workbook.. as it is client data.. but anyways i have got some other solution for that..

1 of 1 people found this helpful
• ###### 9. Re: Time difference is displayed as negative

It would be a good learning for me if you share your solution too. Just calculation part.

1 of 1 people found this helpful
• ###### 10. Re: Time difference is displayed as negative

Hi Mahfooj,

I'll sharre it with you, once i'm done with final view of dashboard..

Can u please help me with how can i get fraction of milli seconds displayed in Tableau..

• ###### 11. Re: Time difference is displayed as negative

I didn't get you. Are you trying to convert your measures to milliseconds or you're trying to show in the format(DD:HH:MM:SS :miliseconds) in your viz?? Let me know your expected output.

Seconds to milliseconds conversion:

Milliseconds to seconds : [Seconds]/1000

Mahfooj

1 of 1 people found this helpful
• ###### 12. Re: Time difference is displayed as negative

Okk.. I'll try with it.. but i want something like 21:25:05:004

Actually i have weather data and i have to plot how is speed changing at various times...

For few timestamps i don't have millisecond part, but for few timestamps, i have milli second part.. Tableau is able to give me below format.. there are further millisecond part extension for these dates..

 21:25:05 21:25:05 21:25:05

I want to see data which also shows millisecond part with one more semicolon in date itself i.e., 21:25:05:005

1 of 1 people found this helpful