Top salespeople that generate over 50% of total regional sales

[#1]

Hi,

Just started using QlikVIew, and trying to count top salespeople that generate over 50% of total sales by region, using example provided by Adam here:

http://community.qlik.com/docs/DOC-3857

Somehow this works:

Sum(if(Aggr(rangesum(above(sum(Sales),0,rowno())), Country, Salesperson)> 100,0,1))

And this does not:

Sum(if(Aggr(rangesum(above(sum(Sales),0,rowno())), Country, Salesperson)> .5*sum(Sales),0,1))

Sum(if(Aggr(rangesum(above(sum(Sales),0,rowno())), Country, Salesperson)> .5*(Aggr(sum(Sales), Country)),1,0))

Apparently I am missing something very basic, would appreciate your help.

Sample QVW attached.

Thanks,

--Nikita

[Anonymous]

Hi,

Try something like this.....

=-sum(aggr(rangesum(top(sum(Sales),1,rowno()-1))/rangesum(top(sum(Sales),1,noofrows())),SalesPerson)<(50)/100))

I took this from a Pareto application I found.

Cheers.

Adam.

[#3]

Thank you, Adam - very helpful!

Last missing bit is the sorting. I want the logic to apply on every level of the drill down.

Even if I apply sorting on load, it may get confused when drills/filters are specified.

Is there any workaround to specify sorting in aggr()?

Thanks,

--Nikita