Calculate rolling correlation between two variables in a time series

Question

Hi

I have a data set which is in the form of a time series with daily averages of data (the first column is the date). I am currently looking at what correlates with one specific variable in the data set (call it column X). I'd like to check if the correlation is time dependent, e.g. if there are periods with OK correlation and other periods with no/low correlation ("switched" on and off for different periods).

My initial thought was to calculate the moving/rolling correlation between column X and the other columns, e.g. for the last 7 days, last month, ...). My questions is twofold:

1) Would this be a good way to check this, or is there a build in analysis in JMP i do not know?

2) If I were to calculate the "rolling correlation" for a time series, how would I program this in the column formula?

In advance: thank's for the help to a newbie!

Jens

JensD · Answer

Thank you both, @txnelson and @SDF1 .

I will try both alternatives so I can see the differences.

Jens

SDF1 · Answer

Hi @JensD ,

I completely agree with @txnelson, you should look into the Time Series Platform, it's great for looking at cross-correlation of time data between two signals. I've used this a lot to compare production data signals with QC data to find time lags.

When you run that platform, you'll put one of the columns as Y, Time Series and the other as Input List (and choose the Autocorrelation Lags to be about 1/2 of the number of rows in your data table). Then, you'll want to run the Cross Correlation utility under the hot button next to the Transfer Function Analysis.

Also, building off what @txnelson wrote for his code, you can even create a table variable (e.g. rowlag) and give it a value and modify his column formula to be:

If( Row() >= :rowlag,
  y = :height[Index(Row()-:rowlag+1, Row())];
  y = :weight[Index(Row()-:rowlag+1, Row())];
  lin = Linear Regression(y`, x`);
  Sqrt(lin[3]["RSquare"]);
)

That way, each time you change the rowlag value in the data table, it will update that column with the correlation values so you can easily test different lags without changing the column formula each time.

Good luck!,

DS

txnelson · Answer

I suggest you look into the Time Series analysis available in JMP.  You can find documentation in the JMP Documentation Library

Help=>JMP Documentation Library

if you want to go ahead with your proposed rolling correlation, here is a formula that can be used to calculate that.  This example is using the Big Class data table from the JMP Sample Data Library

Help=>Sample Data Library

If( Row() >= 7,
	y = :height[Index( Row() - 6, Row() )];
	x = :weight[Index( Row() - 6, Row() )];
	lin = Linear Regression( y`, x` );
	Sqrt( lin[3]["RSquare"] );
)

Attached is the sample data table with the applied formula

example.jmp