Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lematiethibault
Contributor II
Contributor II

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

3 Replies
sunny_talwar

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

sunny_talwar

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

lematiethibault
Contributor II
Contributor II
Author

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