4 Replies Latest reply: Jan 18, 2011 1:05 PM by jameskniep RSS

    Qlikview tail measure

    jameskniep

      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.

        • Qlikview tail measure
          jameskniep

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

            • 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?

                • Qlikview tail measure
                  jameskniep

                   

                  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.

                    • Qlikview tail measure
                      jameskniep

                      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