# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
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-01 0.029 2006-08-01 0.0031 2006-09-01 0.0396 2006-10-01 0.0335 2006-11-01 0.0568 2006-12-01 0.0527 2007-01-01 0.0719 2007-02-01 0.023 2007-03-01 0.0168 2007-04-01 0.0585 2007-05-01 0.0702

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
Highlighted
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
Highlighted
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

Highlighted
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.

Highlighted
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
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.

Highlighted
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.