3 Replies Latest reply: Jan 9, 2017 8:38 AM by Thibault LEMARIE

# Minimum number of values to reach target

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.

IDQuantity

1

1000
21200
3558
4987
53022
691
72221

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

• ###### Re: Minimum number of values to reach target

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

Recipe for a Pareto Analysis – Revisited

• ###### Re: Minimum number of values to reach target

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

Recipe for a Pareto Analysis – Revisited

• ###### Re: Minimum number of values to reach target

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