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

[#1]

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

[johnw]

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.

[#3]

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

[#4]

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

[#5]

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