correlationexcelsql
I have a table with a couple hundred columns and a few hundred thousand rows and want to determine which combination of 5-10 columns best correlate with a target column.
I have SQL Server and Excel but no budget beyond that.
My current plan is:
Is there a better way to do this?
Add a variable to indicate the total. In this case, 6 months is half a year, so we will call this variable "half". Sample data below for columns A,B,C
Now set up a pivot table with both half and month as rows and sales as the value, as shown below.
Apply a lag operator on one time series, with the other fixed, and calculate the coherence of the cross-spectrum achieved against each lag. Find the lag that gives you the maximum coherence and interpret it.
Coherence is computed at each frequency-and hence is a vector. Hence, a sum of a weighted coherence would be a good measure. You would typically want to weight the coherences at frequencies that have a high energy in the power spectral density. That way, you would be measuring the similarities at the frequencies that dominate the time series instead of weighting the coherence with a large weight, when the content of that frequency in the time series is negligible.
http://www.stat.rutgers.edu/home/rebecka/Stat565/lab5-2007.pdf is a good link to look at to get started and http://www.atmos.washington.edu/~dennis/552_Notes_6c.pdf is an excellent introduction to cross-spectral analysis.
Best Answer
You probably want to use R and perform linear regression -- it will give you the linear combination of columns that correlates best with the target column (dependent variable).