topic Quartile in QlikView

Quartile

Wed, 27 Apr 2011 18:26:25 GMT

Customer is asking for Quartile metrics where the quartiles are based on literally 4 equal number of Clients (e.g. each quartile will be based on 1/4 of the number of Clients) and total sales. So quartile 1 will represent the top 25 clients based on sales (assuming 100 clients), quartile 2 will represent the next set of 25 clients based on sales volume, and so forth. Once I know my quartiles, I need to show other metrics based on the quartile as follows:

Quartile	Total Sales	Product A	Product B	Product C
1	%	%	%	%
2	%	%	%	%
3	%	%	%	%
4	%	%	%	%

One other note - Quartile is not a dimension in the data...it's calculated based on the selected data based on 25% increments of client sales.

Thanks,

Kevin

Quartile

danielrozental — Wed, 27 Apr 2011 19:24:53 GMT

You should add a field Quartile (1,2,3,4) not associated with other fields.

Then your expression should be something like

fractile( aggr( sum(Sales), Customer), Quartile/4)

Quartile

Mon, 02 May 2011 18:38:31 GMT

Thanks Dan - just getting back to this and I'm not sure I'm tracking you. I created the Quartile field with values 1-4 but I'm not following the expression. Here's what I'm trying to do...

If I have 100 customers and sorted them in descending order (highest to lowest) based on Sales, Quartile 1 would be made up of the first 25 customers (e.g. Top 25), Quartile 2 would be the next 25 customers, and so on.

Any help would be appreciated.

Thanks!

Quartile

hector — Mon, 02 May 2011 19:07:54 GMT

check this example http://www.qlikfix.com/2010/10/08/decile-analysis/ and change the decile by quartile segments

Rgds

Quartile

Mon, 02 May 2011 20:50:54 GMT

Thanks Hector - pretty close. The data in the example is already aggregated but I'm working with data that needs to be aggregated. I feel like I'm close but I'm struggling a bit with the expressions to accomodate the aggregartion of calculated dimension. Thoughts?

Quartile

hector — Mon, 02 May 2011 21:00:00 GMT


kkelly wrote:
Thanks Hector - pretty close. The data in the example is already aggregated but I'm working with data that needs to be aggregated. I feel like I'm close but I'm struggling a bit with the expressions to accomodate the aggregartion of calculated dimension. Thoughts?<div></div>

Hi, so use the aggr() function in the calculated dimension, something like aggr(Sum(Sales),Customer)

rgds

Quartile

Mon, 02 May 2011 21:07:49 GMT

That's what I've been working through but it's not returning the 1-4 values for the calculated dimension. See expression below:

=if(aggr(sum(Sales), Customer) <= fractile( aggr( sum(Sales), Customer), 0.25), 4,
if(aggr(sum(Sales), Customer) <= fractile( aggr( sum(Sales), Customer), 0.5), 3,
if(aggr(sum(Sales), Customer) <= fractile( aggr( sum(Sales), Customer), 0.75), 2,
1)))

Quartile

hector — Mon, 02 May 2011 21:15:38 GMT

you forgot the "total" modifier/parameter in the second aggr()

fractile( aggr( sum(TOTAL Sales), Customer)

Rgds

Quartile

hector — Mon, 02 May 2011 21:26:32 GMT

from an actual use of that function

='Q'&if(aggr(Sum(Sales),Customer) <= fractile(TOTAL aggr(Sum(Sales),Customer), 0.20), 1,
if(aggr(Sum(Sales),Customer) <= fractile(TOTAL aggr(Sum(Sales),Customer), 0.40), 2,
if(aggr(Sum(Sales),Customer) <= fractile(TOTAL aggr(Sum(Sales),Customer), 0.60), 3,
if(aggr(Sum(Sales),Customer) <= fractile(TOTAL aggr(Sum(Sales),Customer), 0.80), 4, 5))))

this is the calculated dimension, "Total" must be in the fractile section, not in the sum(), my mistake

rgds

Quartile

Mon, 02 May 2011 21:34:38 GMT

That did it!!! Thanks for your help Hector! I think my mind is just fried at the end of the day.

Kevin

Quartile

Mon, 24 Oct 2011 09:39:46 GMT

Hello! How do I replace the "aggr" if I want to include this

'Q'&if(aggr(Sum(Sales),Customer) <= fractile(TOTAL aggr(Sum(Sales),Customer), 0.20), 1,

if(aggr(Sum(Sales),Customer) <= fractile(TOTAL aggr(Sum(Sales),Customer), 0.40), 2,

if(aggr(Sum(Sales),Customer) <= fractile(TOTAL aggr(Sum(Sales),Customer), 0.60), 3,

if(aggr(Sum(Sales),Customer) <= fractile(TOTAL aggr(Sum(Sales),Customer), 0.80), 4, 5))))

into the script, instead of expression?

Re: Quartile

Anonymous — Tue, 06 Oct 2015 19:43:41 GMT

Olá estou enfrentando problemas para fazer o mostrador calcular o terceiro quartil de uma tabela, não consigo fazer calcular

Re: Quartile

dgreenberg — Tue, 06 Oct 2015 20:01:09 GMT

I am having trouble understanding you. Google translates that to "Hello I'm having trouble making the dial calculate the third quartile of a table, can not I calculate"

What type of trouble are you having?