Qlikview tail measure

[#1]

Hi all,

I am trying to calculate a tail measure for a collection of data. This is relatively easy in Excel, I use the following formula on the data column:

=MAX(IF(INDIRECT(Returns!A1)<PERCENTILE(INDIRECT(Returns!A1),1-B3),INDIRECT(Returns!A1)))

Where INDIRECT gives me the selection of data I am working with, and B3 is the confidence interval. What I am trying to get is a way to count X data points in from the tail of my distribution, where 1-X is the user selected confidence interval.

[#2]

Sorry, that should be count*(1-x) data points from the tail end, where x is the confidence interval

[#3]

This looks like you could use Set Analysis. Can you create a simple Excel example and attach it to give us a better idea of what you are trying to do?

[#4]

Date	Close	Daily Rtn
31/12/2009	130.9	25.68%	Median	3.27%	Hist VAR
30/12/2009	132.57	26.93%	Mean	1.98%	=MAX(IF(C:C<PERCENTILE(C:C,1-H3),C:C))	Conf. Int.	99.6%
29/12/2009	131.85	26.15%	StDev	17.31%	Para VAR	Time Horizon	132
28/12/2009	132.31	24.95%	Skew	-0.41	-43.94%
24/12/2009	130.57	22.80%	Kurtosis	-0.02
23/12/2009	130	21.50%		Actual Count	Dev from Mean	Norm Prediction
22/12/2009	129.93	21.07%	-100	0	<-3	3
21/12/2009	128.65	19.54%	-3	34	-2.5 to -3	12
18/12/2009	127.91	18.21%	-2.5	61	-2 to -2.5	39
17/12/2009	127.4	16.45%	-2	129	-1.5 to -2	105
16/12/2009	128.71	18.79%	-1.5	124	-1 to -1.5	219
15/12/2009	128.49	18.82%	-1	316	-0.5 to -1	357
14/12/2009	129.93	20.88%	-0.5	458	0 to -0.5	456
11/12/2009	129.68	20.93%	0	479	0 to 0.5	456
10/12/2009	129.34	21.64%	0.5	427	0.5 to 1	357
09/12/2009	128.39	20.57%	1	236	1 to 1.5	219
08/12/2009	126.8	18.69%	1.5	95	1.5 to 2	105
07/12/2009	127.04	17.23%	2	18	2 to 2.5	39
04/12/2009	127.25	19.73%	2.5	6	2.5 to 3	12
03/12/2009	127.55	21.84%	3	0	>3	3
02/12/2009	127.21	23.59%	100	2383		2382
01/12/2009	127.94	21.82%
30/11/2009	126.35	24.01%
27/11/2009	125.7	22.25%
25/11/2009	127.28	22.33%

The Returns are the main data set here. The excel formula in the box beneath 'Hist VAR' selects the value from the tail according to the user input confidence interval. Perhaps in Qlikview, the data must be sorted from smallest to largest first, then those smaller than the percentile used are taken as a subset, and the maximum of that subset is returned as the value.

[#5]

Here's a bit simpler one:

0.256841		=MAX(IF($K$29:$K$53<PERCENTILE($K$29:$K$53,1-$H$3),$K$29:$K$53))	0.164534
0.269341			55%
0.261481		The column on the left has the data we wish to analyse. The formula above takes this data, sorts it from smallest to largest, and counts the appropriate number of data points in from the tail (i.e. if we had 100 data points, and the percentage were 90, this would sort them, then count 10 up from the bottom). This data point is the point we wish to find.
0.249504
0.22797
0.214953
0.210678
0.19541
0.182053
0.164534
0.18791
0.188182
0.208764
0.20925
0.216402
0.205653
0.186933
0.17228
0.197309
0.218359
0.235888
0.218244
0.240063
0.222525
0.223258