Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
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
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
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

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!

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

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?

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

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

Specialist
Specialist

Hi

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

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

Rgds

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

Not applicable

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

Kevin