topic Quartile (Upper and Lower) in QlikView

How to perform the statistical function: Quartile (Upper and Lower)

Wed, 13 May 2009 20:42:21 GMT

Hi all

I need to find the upper and lower quartile for a given set of records using the most efficient method possible as I need to return the results with sub second response times for millions of rows of data.

As the set changes with the selections the calculation cannot be written into the code.

Therefore do I put the calculation in the expression on each chart or write some sort of function?

Kindest Regards
Paul Bartram

Quartile (Upper and Lower)

Anonymous — Wed, 13 May 2009 22:18:22 GMT

Paul,
I'd recommend to try a macro using API method ParetoSelect or TopSelect. Although the sub-second response looks really tough if you cannot precalculate in the script.

Quartile (Upper and Lower)

Anonymous — Wed, 13 May 2009 22:44:41 GMT

If I understand the issue correctly you should be able to create something like this using the Rank() function.

For example if you have one dimension called Dimension and one expression, Sum(Value) you could use ranking on the Sum(Value) and then compare that ranking to the highest total ranking and only use those that are in the top quartile or any other range really.

The expression would look something like:

if(Rank(Sum(Value),0,1)<=floor(max(total aggr(Rank(Sum(Value),0,1),Dimension))*0.25),1,0)

If the rank for the current dimension value is lower or equal to the highest rank * 0.25, the expression returns 1, else 0.

Best regards,

Jsn

Quartile (Upper and Lower)

Thu, 14 May 2009 00:09:19 GMT

Thank you for your quick responses, however this doesn't account for the following which is normally resolved in a Quartile function

Data
1
2
4
7
8
9
10
12

If the dataset is like the one above, to work out the Quartile you have to say there are (8 datapoints +1)/4 which equal 2.25, therefore both 2 and 4 fall into this. so you have to say (2+4)/2 = 3.5

So the first quartile to the dataset above is 3.5, your equation would select 2 as the Quartile which is incorrect.

I need a quick method to calculate the above possibility.

Kindest Regards
Paul

Quartile (Upper and Lower)

Thu, 14 May 2009 00:25:18 GMT

Thank you for you reply, I think the TopSelect will have the same issue as my comment above, however I'm not sure what ParetoSelect is?

Quartile (Upper and Lower)

johnw — Thu, 14 May 2009 01:18:13 GMT

fractile(Data,.25) and fractile(Data,.75)

Quartile (Upper and Lower)

Anonymous — Thu, 14 May 2009 01:22:56 GMT

Hi Paul,

I'm not sure how any specific quartile calculation would work. The (2+4)/2 should equal 3 though I'm not sure how this relates. The expression would likely need some modification to calculate the cut-off criteria correctly for your desired result.

Quartile (Upper and Lower)

yblake — Thu, 14 May 2009 01:43:34 GMT

in addition to John suggestion to use fractile, you have a document property settings you ay adjust to improve calculation speed :

when not selected, this function interpolates (as Excel centile function does) so I assume this is faster.

when selected, QV use discontinuated values calculation.

Quartile (Upper and Lower)

Anonymous — Thu, 14 May 2009 07:32:03 GMT

Paul,
TopSelect allows to make selections like "top 5 sales reps, where criteria is sum(Sales)".
ParetoSelect allows to make selections like "top sales reps, who bring 75% of sales, where criteria is sum(Sales)".
I think that solution from jsn is functionaly correct (maybe you need some adjustments), but it may be slow because of aggr() function. Worth trying anyway.

How to perform the statistical function: Quartile (Upper and Lower)

Thu, 14 May 2009 20:51:59 GMT

Ok were getting closer, I thought the Fractile function might of been the answer, but it's not quite there.

As a test for anyones function to find the lower quartile and upper quartile for the following data:

11, 4, 6, 8, 3, 10, 8, 10, 4, 12 and 31.

To work this out manully you first have to Order the data, so we get 3, 4, 4, 6, 8, 8,10, 10, 11, 12 and 31.

The lower quartile is the (11 + 1) ÷ 4 = 3rd value in on the dataset. (11 being the count of datapoints)
The upper quartile is the 3 (11 + 1) ÷ 4 = 9th value in on the dataset.

Therefore, the lower quartile is 4, and the upper quartile is 11.

3, 4, 4, 6, 8, 8, 10, 10, 11, 12, 31

I got the above example from the BBC website http://www.bbc.co.uk/schools/gcsebitesize/maths/data/representingdata3hirev4.shtml

Additional ref: http://en.wikipedia.org/wiki/Quartile

Johns, factile function fractile(Data,.25) for the lower quartile returns the answer as 5 and fractile(Data,.75) for the upper quartile returns the answer as 10.5, neither of which are one of the datapoints. The problem arrises when the factile falls between two datapoints, these to adjacent datapoints have to be added together and divided by 2.

Based on the examples from the BBC and Wikipedia, the Quartile function in Excel returns the incorrect result (sames as Johns Factile). Which beggers the question whos right?

Kindest Regards
Paul Bartram

How to perform the statistical function: Quartile (Upper and Lower)

johnw — Fri, 15 May 2009 01:19:09 GMT

From the Wikipedia article, "There is no universal agreement on choosing the quartile values".

That said, and while I'm not a mathematician, the basic point of the first quartile is to split the values into 1/4 and 3/4 of the data points. 4 fails this test. You're either splitting the set into {1} and {6,8,8,10,10,11,12,31) or into {1,4,4} and {4,4,6,8,8,10,10,11,12,31). Basically, there's no reason to include 4 in one set or the other, so it makes no sense to me to include it in the lower set while excluding it from the upper.

5, on the other hand, DOES neatly split the set into 1/4 and 3/4, and is the halfway point between two of our values (4 and 6). So it seems like a logical answer to me, regardless of its correctness.

All that said, if you want the answers 4, 8 and 11 for this data set, all you have to do is go into document properties, main tab, and select "legacy fractile calculation". In other words, QlikView is leaving up to you how you think the quartiles should be calculated since there isn't a universally-agreed upon answer.

That's my unprofessional take on it, anyway.

How to perform the statistical function: Quartile (Upper and Lower)

Fri, 15 May 2009 03:03:42 GMT

Hi Paul,

I'm short of time to help on this, but hopefully the attached example application will point you in the right direction. Wrt how to split your quartiles - well, it seems from the mixed response that the jury is out. I'm unsure as to the relevance of calculating the quartile as the average of the two nearby values - it would seem reasonable to assume that the quartiles will be used for division of result sets, i.e. "retrieve everything in quartile 1 so I can perform calculations on the corresponding record set". Since an in-between data point doesn't actually exist, for calculation purposes it seems to me to serve little value, but then again there may well be valid business reasons why this is of importance.

Best of luck!

Jonas

How to perform the statistical function: Quartile (Upper and Lower)

Fri, 15 May 2009 15:26:39 GMT

Hi John

You are correct, it is upto my client to decide the correct way in which they want to calculate the Quartiles

It appears that calculating quartiles is a common task that can be accomplished in multiple ways, but not all calculations return the same results for the same data. There are many definitions of a quartile as well as multiple descriptive terms (percentile, quartile). The challenge is to determine which method my client wants to use.

Here are some of the possible methods available.

Regards
Paul

How to perform the statistical function: Quartile (Upper and Lower)

Fri, 26 Mar 2010 17:40:24 GMT

Hi Guys ,

Thanks for above discussion , i applied few part from here in my document & got appreciation for same.

Its all because of you all.

Regards,

Bhushan N