Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello the community,
I know my question is not easy to understand but I'll try to be as clear as possible.
Let's say that I have an ID and a quantity of apples related to that ID.
ID | Quantity |
---|---|
1 | 1000 |
2 | 1200 |
3 | 558 |
4 | 987 |
5 | 3022 |
6 | 91 |
7 | 2221 |
This makes a total of 9079 apple.
I need to calculate the minimum number of ID's that I need to reach 80% of my total quantity.
In my example:
80% of 9079=7263.2
3022+2221+1200+1000= 7,443> 7263.2
So my answer in this case would be minimum 4 different ID's(5,7,2,1) to reach 80% of my total quantity.
This is something I manage to do more or LESS in a table (See qvw attached) but I need to display this value (4) in a TextBox
The list of ID depends of a lot of different filters and dimensions so this is not something I can do in the script (I think)
I would really appreciate any help.
Thanks a lot for your time
I hope you have QV 12.1 on your machine. If you do, you can try this:
=Count(ID) - Sum(Aggr(If(RangeSum(Above(Sum(Quantity), 0, RowNo())) < Sum(TOTAL Quantity) * 0.20, 1, 0), (ID, (=Sum({1}Quantity), Asc))))
or this
=Count(ID) - Sum(Aggr(If(RangeSum(Above(Sum(Quantity), 0, RowNo())) < Sum(TOTAL Quantity) * 0.20, 1, 0), (ID, (=Sum({<ID>}Quantity), Asc))))
The idea can be better understood by reading this
I hope you have QV 12.1 on your machine. If you do, you can try this:
=Count(ID) - Sum(Aggr(If(RangeSum(Above(Sum(Quantity), 0, RowNo())) < Sum(TOTAL Quantity) * 0.20, 1, 0), (ID, (=Sum({1}Quantity), Asc))))
or this
=Count(ID) - Sum(Aggr(If(RangeSum(Above(Sum(Quantity), 0, RowNo())) < Sum(TOTAL Quantity) * 0.20, 1, 0), (ID, (=Sum({<ID>}Quantity), Asc))))
The idea can be better understood by reading this
I hope you have QV 12.1 on your machine. If you do, you can try this:
=Count(ID) - Sum(Aggr(If(RangeSum(Above(Sum(Quantity), 0, RowNo())) < Sum(TOTAL Quantity) * 0.20, 1, 0), (ID, (=Sum({1}Quantity), Asc))))
or this
=Count(ID) - Sum(Aggr(If(RangeSum(Above(Sum(Quantity), 0, RowNo())) < Sum(TOTAL Quantity) * 0.20, 1, 0), (ID, (=Sum({<ID>}Quantity), Asc))))
The idea can be better understood by reading this
Hello Sunny and thanks for your reply
Unfortunately, we are not using QV12 yet. The migration is planned to be done soon though.
However, the link for the pareto analysis is already a great way to visualize it so, I think I will start from this point and wait for the upgrade to be done.
Thank you again,
Thibault