topic Calculate time difference and summing the values in Discussions

Calculate time difference and summing the values

Lavik17 — Fri, 09 Jun 2023 16:59:36 GMT

I have a table with a time column with this format: m/d/y h:m:s

I would like to create a new column called CumTimeSec that will show the cumulative time difference up to that row.

For example,

Time

Time	CumTimeSec
5/11/2022 5:30:03 PM	0
5/11/2022 5:30:08 PM	5
5/11/2022 5:30:20 PM	17
5/11/2022 5:30:23 PM	20

I figured I could use this code to calculate the difference between each row, but I'm not sure how to sum the values

dt << New Column( "CumTimeSec",
	Numeric,
	"Continuous",
	formula(  :Time - Lag( :Time) )
);

Re: Calculate time difference and summing the values

Jasean — Thu, 19 May 2022 19:16:37 GMT

Hi Lavik17,

Lag is useful for this application, but you need to use it a couple of times. Note that Dif() takes the difference between the current row and a "lag" row. For example, above you calculate :Time - Lag(:Time). That is the same as Dif(:Time).

Here is an example you can run with JMP sample data. The table just lists a sequence of years, but it will work the same for a time column.

dt = Open("$SAMPLE_DATA/Time Series/SeriesQ.jmp");
dt << New Column("CumTimeYear", Formula(
	If( Row() > 1,
		Lag( :CumTimeYear ) + Dif( :Year )
	, // else first row
		0
	)
));

Re: Calculate time difference and summing the values

jthi — Thu, 19 May 2022 19:38:45 GMT

Haven't used Dif before but you could combine it Col Cumulative Sum

Col Cumulative Sum(Dif(:Time))

Re: Calculate time difference and summing the values

Lavik17 — Thu, 19 May 2022 21:57:20 GMT

Thanks @Jasean !! Worked like a charm!!