Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Sum loop.

Im trying to achieve a sum based on a "cat" and "not cat" field, here is the context.

CompanyUnitsRevenueCategorystatus
A22223434AAA

cat

A34534324BBBcat
A32124567BBBcat
A54563468BBBcat
A12390345CCCcat
A23423789
not cat
A54723567
not cat
A34576285
not cat
A16367823
not cat
A45263962
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