topic Re: Quartile metric calculation using set analysis in QlikView

Quartile metric calculation using set analysis

Wed, 05 Feb 2014 04:21:37 GMT

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.

Re: Quartile metric calculation using set analysis

MK_QSL — Wed, 05 Feb 2014 07:00:30 GMT

Please check enclosed file.

My output is slightly different...

Let me know whether it works f

Re: Quartile metric calculation using set analysis

teempi — Wed, 05 Feb 2014 12:25:38 GMT

Hi,

I don't think you can use set analysis for this because the set analysis condition is only calculated once for the whole dataset, not per dimension. This means that Max(Sales) inside a set analysis will get the max (=16) for the whole dataset.

You may need to calculate those max values beforehand in the script.

-Teemu

Re: Quartile metric calculation using set analysis

Wed, 05 Feb 2014 14:29:49 GMT

Teemu,

Thanks for replying. Is there any other way to achieve this e.g. aggr(), fractlie()?

Re: Quartile metric calculation using set analysis

Wed, 05 Feb 2014 14:31:07 GMT

Hi Manish,

appreciate your help. But the xpressions does not appear to work for the reason Teemu has mentioned. I changed the input numbers slightly and reloaded qvw. Check the output. They are wrong.

Re: Quartile metric calculation using set analysis

teempi — Thu, 06 Feb 2014 07:10:11 GMT

Hi,

You may be able to use some sort of If statement with aggr etc. but I'm not sure. And personally I hate If statements because they can be very slow. If the row count is not an issue I would probably calculate those dimension specific max values in the script. It would make the expressions a bit simpler too I think.

Sorry but I can't provide you with an example at this time. I'll see if I have time later today.

-Teemu

Re: Quartile metric calculation using set analysis

Thu, 06 Feb 2014 17:58:49 GMT

I agree with Teemu, It is efficient to do it in Script.

Other dirty way is to create Variables, But you will end up with 4 * 5 * 3 variables

Assuming 4 regions, 5 Titles and 3 Quartiles. Use those variables in the expression.

It is interesting to know if some one solves this.

Re: Quartile metric calculation using set analysis

Thu, 06 Feb 2014 18:29:10 GMT

Sudheer, Teemu,

Script may not be an option as I have 30 other dimensions that can change the combination. For e.g. Month, Year, Dept, Region etc. User can make selection on any of these dimensions and calculation needs to reflect the selections.

I shared a simplified data set for illustrating the problem.

Re: Quartile metric calculation using set analysis

michael_anthony — Fri, 07 Feb 2014 02:33:58 GMT

Try the following expressions:

Q1:

Sum(Aggr(If(Sum(Sales) >= Max(TOTAL <Title,Region> Sales) * 0.75,Sum(Sales)),Region,Title,Employee))

Q2:

Sum(Aggr(If(Sum(Sales) >= Max(TOTAL <Title,Region> Sales) * 0.5 And Sum(Sales) < Max(TOTAL <Title,Region> Sales) * 0.75,Sum(Sales)),Region,Title,Employee))

Q3 and Q4 same as these with the factors changed. This gives the output your after.

It is also customisable so that if your dimensions changes, you can modify the functions to reflect in the appropriate places.

If you introduce other dimensions particularly if they reduce the lowest level below employee may need to play with the functions a little.

Re: Quartile metric calculation using set analysis

Fri, 07 Feb 2014 02:46:22 GMT

Bingo!! that works perfectly!

Thanks Michael.

Re: Quartile metric calculation using set analysis

barryharmsen — Fri, 07 Feb 2014 11:21:12 GMT

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 )

Re: Quartile metric calculation using set analysis

Fri, 07 Feb 2014 14:42:36 GMT

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.

Re: Quartile metric calculation using set analysis

Thu, 20 Feb 2014 09:57:17 GMT

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.

Re: Quartile metric calculation using set analysis

barryharmsen — Thu, 20 Feb 2014 11:08:21 GMT

Glad you found it useful!

Re: Quartile metric calculation using set analysis

Thu, 20 Feb 2014 14:56:38 GMT

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

Re: Quartile metric calculation using set analysis

Fri, 21 Feb 2014 15:53:24 GMT

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.

Re: Quartile metric calculation using set analysis

Fri, 21 Feb 2014 15:56:21 GMT

Michael,

Thanks for the detailed explanation.