topic Re: Correlation using user specified # of recent observations in QlikView

Correlation using user specified # of recent observations

Anonymous — Mon, 28 Nov 2016 18:18:29 GMT

Hi Everyone,

I am trying to create a chart expression for a pivot table that calcs the correlations between 2 columns based on the # of observations input by the user, ie: what is the correlations based on the most recent 200 observations. The attached qvw has some sample data and an input box already built. The correlation should be based on the columns LogReturn_1 & LogReturn_2 for each combination of IndexShortName_1 & IndexShortName_2. I have tried using RangeCorrel and saw several examples using the Count(),Top(),Above() but I am not getting any results.

Re: Correlation using user specified # of recent observations

johnw — Tue, 29 Nov 2016 01:05:43 GMT

This seems to work:

correl({1<EquityIndex_HistReturnsCorrel.ValueDate={">=$(=date(firstsortedvalue({1} EquityIndex_HistReturnsCorrel.ValueDate,-EquityIndex_HistReturnsCorrel.ValueDate,vCorrelationDays),'DD/MM/YYYY h:mm:ss TT'))"}>} EquityIndex_HistReturnsCorrel.LogReturn_1,EquityIndex_HistReturnsCorrel.LogReturn_2)

Re: Correlation using user specified # of recent observations

Anonymous — Tue, 29 Nov 2016 12:10:10 GMT

Thank you very much John. I will give this a try.

Re: Correlation using user specified # of recent observations

Anonymous — Tue, 29 Nov 2016 13:22:27 GMT

Hi John,

I tried the expression and I am not getting any results...In my mind, this is how I thought the calc should go:

1. Each row in the table has a RowNum goes from 2 to whatever the max row number is for each combo of IndexShortName_1 & IndexShortName_2

2. The user specifies how many "rows" of recent observations they want to use for the correlation calc

3. The expression should filter from each combo of IndexShortName_1 & IndexShortName_2 all of the rows where the RowNum >= Max(RowNum) for the combo minus the vCorrelationDays

ie: For SPX|NKY the max row num is 11206 and if the vCorrelationDays is 100 then the correlation calc should use rows 11106 to 11206 inclusive. Another combo may have a different max rows value but should still use the most recent 100 days.

I attached another qvw that has all of the combos.

Re: Correlation using user specified # of recent observations

johnw — Tue, 29 Nov 2016 16:52:20 GMT

I was using date for "most recent", so I'll need to switch to row number, plus set analysis "can't" be sensitive to the dimensions of a table, so can't directly be used to pick the correct row numbers, so I'll need to come up with something different for that reason as well.

I'm thinking the right approach would be a script change to assign a reverse of the row numbers, the most recent being 1 and then incrementing going back in time for each combination. Easily done with a second load with an order by and using the previous() function to see if we need to start our count over, and to keep track of our count. Then we can go ahead and use set analysis for performance, and the set analysis would be simple. This assumes that which records count as the "most recent 100" shouldn't change based on selections, but that was my assumption with the previous solution too. So that's my suggestion, then, and it isn't something I can test since I can't read your databases. I can still give it a shot if you need, write some untested code, but maybe what I've written in this paragraph is enough to get you on track.

Edit: peek() function to keep track of the count, previous() refers to the input table, peek() to the output table