Group Customers Into 3 Tiers Based On Revenue?

billbois — Thu, 14 Aug 2014 18:19:40 GMT

This one's a headscratcher. I have a new requirement to display a list of our customers, their total net revenue, and a new column that shows their "tier." The tier is whether their revenue is in the top third, the middle third, or the bottom third; but it's not quite that simple. It has to be the sum of several customers that fill out the top third, even if that sum is bigger than a third.

For instance, if our top two customers have 20% and 15% of the total revenue respectively, that totals 35% which is more than a third. The the middle tier might be several customers that also add up to more than a third. The bottom tier would therefore be less than a third.

I've tried this in a straight table using something like:

=IF(RangeSum(Above(Sum(NetRevenue),0,RowNo())) >= (Sum(TOTAL NetRevenue) * 0.66), '1',
IF(RangeSum(Above(Sum(NetRevenue),0,RowNo())) >= (Sum(TOTAL NetRevenue) * 0.33), '2', '3'))

But, even if this were close to correct, it would only work if the table is sorted by NetRevenue descending, and you can't sort by expressions and use Intra Chart functions.

I've tried many other variations but haven't stumbled onto a solution. Any suggestions?

Thanks!

Re: Group Customers Into 3 Tiers Based On Revenue?

maxgro — Thu, 14 Aug 2014 18:46:47 GMT

if I understand you can adapt this (changing ABC %)

http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/09/pareto-analysis

Re: Group Customers Into 3 Tiers Based On Revenue?

billbois — Thu, 14 Aug 2014 19:04:55 GMT

Yes! I think that will do it. Thanks a million!

topic Re: Group Customers Into 3 Tiers Based On Revenue? in QlikView

Group Customers Into 3 Tiers Based On Revenue?

Re: Group Customers Into 3 Tiers Based On Revenue?

Re: Group Customers Into 3 Tiers Based On Revenue?