In this thread http://community.qlik.com/message/160141#160141 i came accross a demo that show you how to get the TOP N% depending on the sliding bar that you set. This works great in my model to show how many customers generate 80% of the business - i count the customers as in demo.
Now i have a request to add another dimension Territory in so that the user can see per Territory how many customers generate 80% of business. This mean i must show all territories, and in each territory the customers in that territory makes up 100 % but it must show me also the 80% . I need to count these that makes up 80% of business
In a pivot i would work great if i can group it by territory. In the example i have a table (blue) and n pivot(red). In the table the split dont occur between territories to indicate which customers makes up 80% per territory. The pivot would work brilliant if i can get it rolled up to show 100% for each territory and when you expand it, it shows you the customer percentage split that makes up for instance 80% and down to 100% per territory. The whole exercise is to count the customers per territory that makes up 80% of business but also show you the rest of customers.
If this is not possible in a pivot is there a way to do it in a table ?
Help would really be appreciated, i am stuck with this now for a while.
Thank you for your reply, i did use you <Territory> and this work great in the pivot. But i still need to count the customers in this territory that fall in the bracket of 80% for instance. I wrote the expression with aggr and this sum the total correct but flag pareto field is not flagging the correct customers so the sum is calculating the wrong customers. Only customers that falls into the bracket of 80% must be flagged with a 1.
sum ( aggr ( If ( Rangesum ( Above ( sum ( [Sales Amount] ) ,0 , Rowno() )) / sum ( Total <Territory> [Sales Amount] ) <= v_Pareto_Persentage , 1 , 0) , Territory , Customer ))
Does anyone have advise here i have tried several variations of AGGR but with no success. The total per territory of the column flag pareto is now correct but my if statement flag the wrong customers now. It must just flag the customers that falls in the 80% bracket.