Gareth Knopp 2 Posted March 15, 2019 Share Posted March 15, 2019 Hi, I have a Data table with multiple different vehicles (Serial Number - SN) in it, with data points on a specific DateTime. So the unique identifier is DateTime & SN. I'm trying to find a way to work out the number of minutes/amount of time that something happens for. So, for example, a setting or flag is recorded when an event occurs, that record has a time stamp, I want to compare to the previous entry timestamp, work out the difference between them and display this in minutes! I created a TimeSpan Column of the DateTime column TimeSpan([DateTime]) Then I looked at using the OVER function: [Time_Span] - Min([Time_Span]) OVER (Previous([DateTime])) But this doesn't really seem to work! Link to comment Share on other sites More sharing options...
Richard Pobi Posted March 15, 2019 Share Posted March 15, 2019 If the SN is also a primary key/unique identifier then use the SN instead of the datetime. [Time_Span] - Min([Time_Span]) OVER (Previous([sN])) Link to comment Share on other sites More sharing options...
Richard Pobi Posted March 15, 2019 Share Posted March 15, 2019 If SN & DateTime falls under one column. eg. Column1 then use it for the OVER function [Time_Span] - Min([Time_Span]) OVER (Previous([Column1])) If they are two different columns then add it to the expression. [Time_Span] - Min([Time_Span]) OVER (Previous([sN]), (DateTime)) Check the link below for help https://docs.tibco.com/pub/sfire-bauthor/7.8.0/doc/html/en-US/GUID-35B6C76C-EF02-49BF-9A34-EBEB20434ACC.html Link to comment Share on other sites More sharing options...
Gareth Knopp 2 Posted March 18, 2019 Author Share Posted March 18, 2019 Hi richierich, The Unique ID is a combination of two columns. - SN & TimeDate - but I am getting errors, with the above formula. [Time_Span] - Min([Time_Span]) OVER (Previous([Asset]), (DateTime)) Spotfire is not happy with the ',' after [Asset] [[{"fid":"164321","view_mode":"default","fields":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false,"height":"","width":""},"type":"media","field_deltas":{"1":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false,"height":"","width":""}},"attributes":{"class":"media-element file-default","data-delta":"1"}}]] I should add that the reason these are formatted this way is that each vehicle creates its own record which is the input for the master table, so each asset does not have a unique Data Table. Thanks, Gareth Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now