Im trying to achieve a sum based on a "cat" and "not cat" field, here is the context.
| Company | Units | Revenue | Category | status |
|---|
| A | 222 | 23434 | AAA | cat |
| A | 345 | 34324 | BBB | cat |
| A | 321 | 24567 | BBB | cat |
| A | 545 | 63468 | BBB | cat |
| A | 123 | 90345 | CCC | cat |
| A | 234 | 23789 |
| not cat |
| A | 547 | 23567 |
| not cat |
| A | 345 | 76285 |
| not cat |
| A | 163 | 67823 |
| not cat |
| A | 452 | 63962 |
| not cat |
Ok so what im looking in doing is simple yet complicated, i get the percentage of "not cat" units, so if it the percentage of "not cat" units is smaller then "user inputs value" i want qlikview to start summing the "not cat" Units from biggest Units to smaller, and when it achieves the "user inputs value" number i want it to give me the value of the last Units it summed.
Basically, lets say "not cat" percentage of Units is 60%, so qlikview sums all "cat" Units plus the highest "not cat" Unit in this case, 547, and it calculates if with this "not cat" sum it achieves the required value, if not it sums the next highest value, which is 452, then it calculates the percentage again and if it meets the requirements then it will display "452" in a box.
Then after this it calculates if the percentage of "not cat" Revenue also achieves the user inputed percentage, but this calculation is done with the revenues added from the Units it already added from this first calculation, so if it achieves the required value, its fine, if not then it starts adding the highest revenues like in the first calculation but this time for revenues, and when it achieves the desired percentage it will show on a table the full rows of the revenues it had to sum.
Hope it makes sense, quick EG.
lets say desired percentage is 10% "not cat", so it checks percentage of "not cat" lets say its 50%( sum(not cat)/sum(all), starts summing highest "not cat" units untill 10% is achieved, then it returns the last summed value, now it will also calculate the percentage of "not cat" Revenues with the new values still in memory, and it will start adding highest revenues untill the 10% is achieved, but this time returns a table with the rows of the Revenues only it needed to add.
Hope it makes sense.
Many Thanks
