topic Re: Rolling correlations in QlikView

Rolling correlations

m_monfort — Mon, 22 Oct 2018 12:56:53 GMT

Hi all,

I wanted to know if it is possible to do a rolling correlations chart like that which is available on the financial app Koyfin?

Below is a screenshot where AAPL (Apple) has been chosen to be compared with SPY (S&P 500 index). The bottom line chart is the time series of daily share price for both but the chart above is a rolling correlation chart with a look back period (N) of 21 days.

I wanted to know if there was a way to do something like this on time series data in QlikView or Qlik Sense so that you can choose N amount of look back days?

Please help - thank you!

Re: Rolling correlations

niclaz79 — Mon, 22 Oct 2018 13:30:25 GMT

Hi,

Hard to define without seeing your exact data, but using RangeSum() and Above() is a good bet.

Something like

Avg(RangeSum(Above(Sum(YourValue),0,vDaysToAverage))) Should work, where YourValue is the value you are calculating and vDaysToAverage is over how many days the average is for.

Hope it helps!

Re: Rolling correlations

MarcoWedel — Mon, 22 Oct 2018 19:37:48 GMT

please post some sample data and your expected result.

Re: Rolling correlations

m_monfort — Tue, 23 Oct 2018 04:36:15 GMT

Hi all,

Thanks for the replies. Please find attached an example time series with 2 stocks in there (it's not for the image above as that was just a screenshot from an app where I'd seen this being done).

Here's the data.

Re: Rolling correlations

MarcoWedel — Tue, 23 Oct 2018 22:15:32 GMT

maybe you could use an expression like

RangeCorrel(Above([Stock 1],0,21),Above([Stock 2],0,21))

hope this helps

regards

Marco

Re: Rolling correlations

m_monfort — Wed, 24 Oct 2018 10:39:29 GMT

Thanks everyone - the RangeCorrel works a treat.