Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

jcfrasco
Contributor

autocorrelation

Hi

I would like to calculate the auto or serial correlation of a return time series (using monthly returns). I have dates and returns, and would like to calculate the correlation at various lags (which I will make my dimension e.g. 1, 2, 3, ..

How can I pass through the values with the appropriate lags?

For example, if I had the following data:

2006-07-010.02900
2006-08-010.00310
2006-09-010.03960
2006-10-010.03350
2006-11-010.05680
2006-12-010.05270
2007-01-010.07190
2007-02-010.02300
2007-03-010.01680
2007-04-010.05850
2007-05-010.07020

I want Correl(0.00310,0.02900,0.3960,0.00310,0.03350,0.03960...)

i.e. the first value of every pair starts at the second value and goes until the last value, while the second value of every pair goes from the first value to the second last value.

Any help would be appreciated.

Thanks.

Joao

1 Solution

Accepted Solutions
MVP
MVP

Re: autocorrelation

Are you looking for something like attached?

Using a chart with dimension

=aggr(rank(@1)-1, @1)


and expression

=rangecorrel (top(@2, 1, noofrows()-(Rowno()-1) ), top(@2, rowno(), noofrows()-(Rowno()-1 ) ))

[@1, @2 are your two fields]

Note that your examined data ranges get smaller and smaller when approaching the end of your time period, so the correlation result itself gets less significant for like values.

Regards,

Stefan

5 Replies
MVP
MVP

Re: autocorrelation

Are you looking for something like attached?

Using a chart with dimension

=aggr(rank(@1)-1, @1)


and expression

=rangecorrel (top(@2, 1, noofrows()-(Rowno()-1) ), top(@2, rowno(), noofrows()-(Rowno()-1 ) ))

[@1, @2 are your two fields]

Note that your examined data ranges get smaller and smaller when approaching the end of your time period, so the correlation result itself gets less significant for like values.

Regards,

Stefan

jcfrasco
Contributor

Re: autocorrelation

Thank you so much for this. I don't know how it works but it does. I don't in fact want to do this for all lags. I have a dimension called Lags, which I want to use and will try to amend the above to incorporate this, if I can figure out what the formulas above do.

Thanks again.

Joao.

MVP
MVP

Re: autocorrelation

You're welcome.

The dimension is essentially just using your time field, but displaying a continuous number starting from 0 (which you can also interpret as lag). You can achieve these relabeling using aggr() function with the rank() function (rank() just returns the rank for the numerical value of your date field (QV needs to interprete your date values correctly as numerical to make this work))

The expression is using a variant of the correl function (rangecorrel() ), and as argument two chart inter record functions (top() ) for the x and y coordinates. As argument to the top() function, I use other chart inter record functions noofrows() and rowno() to calculate the set of records we need to address.

Try to understand how this works, if you have any questions after trying, I will try to answer these.

Regards,

Stefan

Highlighted
jcfrasco
Contributor

Re: autocorrelation

Thanks for this explanation.

Is it important that date and lag are related in the dimension, so that you can then use it in the expression? Could I use any dimension with the numbers from 0 to 10 say to achieve the same thing, or would the top function now not know what I was referring to because the numbers (lags) are not connected to dates?

Because of your point of the autocorrelation not being meaningful, as my number of observations (used in correl) fall to 2, I actually only want to do the calculation for the first few lags. Could I achieve this with the dimension and expressions already provided, or do I need to change things significantly? I tried to just limit the number of items that I show in expression, which works, but I’m left with all the other lag terms with no data.

Thanks again.

Joao.

STANLIB disclaimer and confidentiality notice:

This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of STANLIB. Any views or opinions presented are solely those of the author and do not necessarily represent those of STANLIB. This e-mail is subject to terms available at the following link: http://www.stanlib.com/Pages/Disclaimer.aspx.

STANLIB is an authorised financial services provider.

jcfrasco
Contributor

Re: autocorrelation

I’ve managed to do what I wanted by just limiting the x-scale to the first 13 values.

Thanks again immensely for your help.

Joao.

STANLIB disclaimer and confidentiality notice:

This e-mail may contain information that is confidential, privileged or otherwise protected from disclosure. If you are not an intended recipient of this e-mail, do not duplicate or redistribute it by any means. Please delete it and any attachments and notify the sender that you have received it in error. Unless specifically indicated, this e-mail is not an offer to buy or sell or a solicitation to buy or sell any securities, investment products or other financial product or service, an official confirmation of any transaction, or an official statement of STANLIB. Any views or opinions presented are solely those of the author and do not necessarily represent those of STANLIB. This e-mail is subject to terms available at the following link: http://www.stanlib.com/Pages/Disclaimer.aspx.

STANLIB is an authorised financial services provider.