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

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

• ###### Qlikview tail measure

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

 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:C3 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.

• ###### Qlikview tail measure

Here's a bit simpler one:

 0.256841 =MAX(IF(\$K\$29:\$K\$53