Quartile

[#1]

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

[danielrozental]

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)

[#3]

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!

[hector]

Hi

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

Rgds

[#5]

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?

[hector]

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

[#7]

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)))

[hector]

Hi

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

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

Rgds

[hector · Accepted solution]

Hi

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

[#10]

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

Kevin