Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like some assistance on Decile analysis to segment 211customers into 10 equal parts based on profits.
I followed an example posted by Barry at the link http://www.qlikfix.com/2010/10/08/decile-analysis/
Everything works except my customers are not equally segmented. I was expecting to see 21 customers per segment. It appears most customers appears in more than one segment. I would appreciated if some review attached app and suggest a fix.
Try this as your calculated dimension:
=Aggr(
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.1), 10,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.2), 9,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.3), 8,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.4), 7,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.5), 6,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.6), 5,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.7), 4,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.8), 3,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.9), 2, 1))))))))), Customer)
Try this as your calculated dimension:
=Aggr(
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.1), 10,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.2), 9,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.3), 8,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.4), 7,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.5), 6,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.6), 5,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.7), 4,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.8), 3,
if(Sum(Profit) <= fractile(TOTAL Aggr(Sum(Profit), Customer), 0.9), 2, 1))))))))), Customer)
Thank You so much. Your script worked.
Awesome!!! Please close this thread by marking correct response if you got what you were looking for
Richard -
I am happy that you took time out of your busy schedule to mark my response as correct response, but I think it is important that you mark the response which is actual the correct response. The reason I say this is because other people might run into similar issues and will land on this thread for answers and we wouldn't want them to see my request to you for marking the correct response as the correct response. They should actually see the response which is actually correct. Together we can make this community a much better place.
Thanks,
Sunny