Using a formula column, how do I find a value in a subset of the data based on other columns

[#1]

If I have two different time series in a table, and there is a findable event in both series, but they occur at different times. How can I specify a formula column to subtract off the time such that the finable events start at time zero?

[#2 · Accepted solution]

If the signal is very clean, you can just use lag to find the jump - otherwise you have to invest more effort to remove noise etc.

Then you just have to subtract the offset of the jump from your original time.

Col ... aggregations like Col Maximum can be used to spread one value to all rows - which then can be subtracted.

A second argument of the Col ... aggregation can be used as GroupBy argument (1 maximum per group) to subtract individual offsets for the 2 traces.

t1 = random integer(1000);
t2 = random integer(1000);

dt = new table("test",
add rows(2000),
New Column( "time",  Set Each value(Mod(row()-1,1000)) ),
New Column( "sample",  Set Each value(floor((row()-1)/1000)+1) ),

New Column( "trace",  Set Each value(if(:time<match(:sample,1,t1,2,t2),1,0 )))
);

New Column( "marker",  Set Each value(if(lag(trace)==1 & :trace==0,time,.)));

New Column( "time_adjusted",
	set each value( :time - Col Maximum( :marker, :sample ) ),
	Set Selected
);

Graph Builder(
	Variables(
		X( :time ),
		X( :time_adjusted ),
		Y( :trace ),
		Group Y( :sample )
	),
	Elements( Position( 1, 1 ), Line( X, Y, Legend( 14 ) ) ),
	Elements( Position( 2, 1 ), Line( X, Y, Legend( 15 ) ) )
)

[#3]

This solved my problem exactly!

I think col ... aggregations was something I was sorely missing in my mental toolbox.

Thank you!