topic Re: Another Pareto Question! in App Development

Another Pareto Question!

ChrisCooil — Sat, 16 Nov 2024 19:59:16 GMT

So I've searched extensively on here to find an answer to my question but I'm coming up empty. I have a simple table of invoices tagged to suppliers. I want to count the number of suppliers that make up the top 80% of invoices.

SupplierId	Value
1	100
2	130
3	21
1	344
2	56
2	12
1	322

I've tried using Aggr and RangeSum but can't get it quite right. I don't think I can sort the Aggr by sum(value) to force the cumulative sum to not be based on load record number. I'm using QlikSense Cloud so no macro enabled solution is going to be right for me.

Re: Another Pareto Question!

Vegar — Sun, 29 Sep 2019 22:02:49 GMT

Maybe this discussion can be a interesting read for you? number of customer who makes 80% of turnover

Good luck!

Re: Another Pareto Question!

ChrisCooil — Mon, 30 Sep 2019 00:04:45 GMT

Thanks, I’d seen this one but I’m not sure how I’d translate this

1 - sum(aggr(sum(if(Week<=N,Amount))/sum(total Amount)<Limit,N))

into an expression that would work with my data.

Re: Another Pareto Question!

Vegar — Mon, 30 Sep 2019 07:35:48 GMT

Try something like this as suggested by @johnw here:

=-sum(aggr(rangesum(top(sum(Value),1,rowno()-1))/rangesum(top(sum(Value),1,noofrows())),SupplierId)<0.8)

Re: Another Pareto Question!

ChrisCooil — Mon, 30 Sep 2019 13:24:45 GMT

Thanks, so that is counting the number of suppliers as they are ordered in the load. I need it to order by sum(value) descending. So close!

Re: Another Pareto Question!

sunny_talwar — Mon, 30 Sep 2019 13:30:12 GMT

Have you seen at this thread -> Recipe for a Pareto Analysis