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.
