Quartile metric calculation using set analysis

[#1]

Hi All,

I need to create a pivot table by grouping the metric (sales) into 4 groups (Quartiles).

Q1 - Sum of all sales where Sales > (Max(Sales)*0.75)

Q2 - Sum of all sales where Sales >= (Max(Sales)*0.5) and Sales < (Max(Sales)*0.75)

Q3 - Sum of all sales where Sales >= (Max(Sales)*0.25) and Sales < (Max(Sales)*0.5)

Q4 - Sum of all sales where Sales < (Max(Sales)*0.25)

e.g: For NAM and Dir combination Max(Sales) is 15 and Max(Sales)*0.75 is 11.25

Q1= 15+12 =27

Q2= 10

Q3=0

Q4=0

Data set

Region	Title	Employee	Sales
NAM	Dir	E1	10
NAM	Dir	E2	15
NAM	Dir	E3	12
NAM	VP	E4	16
NAM	VP	E5	13
NAM	VP	E6	10
NAM	VP	E7	2
ASIA	VP	E8	10
ASIA	VP	E9	5
ASIA	VP	E10	1

Expected Output:

Region	Title	Q1	Q2	Q3	Q4
NAM	Dir	27	10	0	0
NAM	VP	29	10	0	2
ASIA	VP	10	5	0	1

I tried below expression but does not produce correct results.

Q1= sum ({$<Sales={'>= $(#=max(Sales)*0.75)'}>}Sales)

Q2= sum ({$<Sales={'< $(#=max(Sales)*0.75) >= $(#=max(Sales)*0.5) '}>}Sales)

Q3= sum ({$<Sales={'< $(#=max(Sales)*0.5) >= $(#=max(Sales)*0.25) '}>}Sales)

Q4= sum ({$<Sales={'< $(#=max(Sales)*0.25)'}>}Sales)

Please advice.

[barryharmsen]

Maybe a useful addition as well: Decile Analysis in QlikView: Decile analysis » The Qlik Fix! The Qlik Fix!

(which can be easily changed to quartiles of course )

[#3]

Hi Barry..thanks for the comment. I regularly read your blogs. I did try to use fractile to solve this requirement after going through your blog on decile analysis. But could not come-up  with the right expression. If you could provide the right expression that will be great.

[#4]

I've been searching for the solution to a similar problem and could not figure out a way using set analysis since it does not seem to work with aggregates at the intermediate level.

Barry's solution works perfectly so thanks for that, the only modification I needed to make was to replace the variables with their aggregates since my data is at individual sales level rather than summary level as in his example. So instead of:

=if(Profit <= fractile(TOTAL Profit, 0.1), 10...

I used:

=if(aggr(Profit, customer) <= fractile(TOTAL aggr(Profit, customer) 0.1), 10...

Also need to point out that the OPs method, while I'm sure it could be useful for analysis, is not splitting the data into quartiles at all. For example If the data is right skewed by one high employee:

E1 = 5

E2 = 3

E3 = 4

E4 = 22

Then the first three employees would fall into quartile 1 by that definition (22 * 0.25 = 5.5), which is wrong. Using the fractile() method works though.

[barryharmsen]

Glad you found it useful!

[#6]

Fractile appears to be a very useful function. Unfortunately, there is not enough documentation on the same.  I have gone through all discussions (listed below) on Fractile function in the forum, but could not understand how it works, what it does.

Can some explain Fractile in simple terms.

http://community.qlik.com/thread/67578

http://community.qlik.com/thread/36883

Use of Fractile() | Qlik Community

[#7]

Fractile is essentially QlikView's term for percentiles.

If you're not familiar with these, they are typically used to split data into equal sized parts or work out how much of the data lies above / below a certain point. Percentiles lie between 0 and 100 since they are defined in percent, whereas fraciles lie between zero and one, so the 50th percentile and fractile 0.5 are equivalent.

For example the 50th percentile (or 0.5th fracile) is the median and means that 50% of the data lies above and below that point. The 75th percentile (fractile 0.75) is the upper quartile and means that 75% of the data lies below this point and 25% lies above. The 100th percentile is the maximum value and the 0th percentile is the minimum. If you want to split the data into deciles (10 groups) the first group lies between fractile 0 and 0.1, the second group between fractile 0.1 and 0.2, and so on...

To use the function, simply enter fractile([variable], [fractile]), e.g. fractile(weight, 0.75) would give the upper quartile of the variable weight.

I'd suggest creating a file of 100+ rows of random sample data ranked in descending order to test this on.

Note that there are different definitions for how percentiles work for variables falling on a boundary. I'm not sure how QlikView deals with these as I don't tend to use it for small datasets.

You may need to use the aggr() within the fractile() function if you're data is at a lower level than you want to analyse it on, for example to aggregate sales by employee before working out who are the best performers.

[#8]

Michael,

Thanks for the detailed explanation.