Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Quartile

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:

QuartileTotal SalesProduct AProduct BProduct 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

1 Solution

Accepted Solutions
hector
Specialist
Specialist

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

View solution in original post

12 Replies
danielrozental
Master II
Master II

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)

Not applicable
Author

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

Hi

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

Rgds

Not applicable
Author

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


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

Not applicable
Author

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

Hi

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

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

Rgds

hector
Specialist
Specialist

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

Not applicable
Author

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

Kevin