Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Qlikview tail measure

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.

4 Replies
Not applicable

Qlikview tail measure

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

Not applicable

Qlikview tail measure

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?

Highlighted
Not applicable

Qlikview tail measure

DateCloseDaily Rtn
31/12/2009130.925.68%Median3.27%Hist VAR
30/12/2009132.5726.93%Mean1.98%=MAX(IF(C:C<PERCENTILE(C:C,1-H3),C:C))Conf. Int.99.6%
29/12/2009131.8526.15%StDev17.31%Para VARTime Horizon132
28/12/2009132.3124.95%Skew-0.41-43.94%
24/12/2009130.5722.80%Kurtosis-0.02
23/12/200913021.50%Actual CountDev from MeanNorm Prediction
22/12/2009129.9321.07%-1000<-33
21/12/2009128.6519.54%-334-2.5 to -312
18/12/2009127.9118.21%-2.561-2 to -2.539
17/12/2009127.416.45%-2129-1.5 to -2105
16/12/2009128.7118.79%-1.5124-1 to -1.5219
15/12/2009128.4918.82%-1316-0.5 to -1357
14/12/2009129.9320.88%-0.54580 to -0.5456
11/12/2009129.6820.93%04790 to 0.5456
10/12/2009129.3421.64%0.54270.5 to 1357
09/12/2009128.3920.57%12361 to 1.5219
08/12/2009126.818.69%1.5951.5 to 2105
07/12/2009127.0417.23%2182 to 2.539
04/12/2009127.2519.73%2.562.5 to 312
03/12/2009127.5521.84%30>33
02/12/2009127.2123.59%10023832382
01/12/2009127.9421.82%
30/11/2009126.3524.01%
27/11/2009125.722.25%
25/11/2009127.2822.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.

Not applicable

Qlikview tail measure

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.26934155%
0.261481The 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