Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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