Loop through chart(straight table) and perform calculations

[#1]

Hi Guys,

I have a kind of unusual task for me. I need to loop through chart values and asign records to specific flag according to calculation results. Here is my chart that i need to make calculations:

What i need to do now is to sum the entire colum Profit and dive it by 3. This is ok i can aproach it.

when io sum and divide by 3 i get 1 066 491/3=355 497.

What i have to doo now is to start looping from the first record(records are sorted by largest to smallest) and start summing until the sum of summed records is >= to 355 497(the number i got when divided the whole sum).

So in this case Salesman1, Salesman2, Salesman 3 fulfill my condition and i have to mark them as a flag A.

Then when i have Found A records already i need to continue where i stopped. I will start summing from Salesman4,salesman5,salesman6,salesman7 and again when i reach 355 497 i stop and mark those records as a flag B

all the remaining records below last record with B i mark as a C.

This is like a simple for or while loop but how to write it in qlikview? Can i approach it via expressions or have to do it in load script. If you guys could help me i would save my next days:)

Thanks

[#2]

I can loop now through the fields perform calculations.

I also added a input field that references to rownumber and i can edit that input fields but i can imput in the field only numeric values.

Anybbody knows how to input a string value into inputfield?

[Anonymous]

can you please post the data in an excel and also how you want your output to look like in the excel so that i can be able to help you .

[#4]

Hi,

This is how i want to look it like:

and this is the data from chart table

[Anonymous]

This is really tricky

[#6]

Hey Julius,

If the above expressions that you have like net price, cost, profit can all be calculated on the back-end, then the answer you are looking for can be done on the back-end.

Attached with solution.

Hope it guides you in the right direction.

Thanks

AJ

[prabhuappu · Accepted solution]

Hi Julius,

if you want to flag the values dynamically at the front end you have to use inter-chart function.

we need to use some additional columns to do that. it is just similar to what Ajay suggested.

i think attached application will help you to do that. if work more on that you can simplify even more.

regards,

Prabhu Appu

[Colin-Albert]

Have a look at this blog by Henric Cronström

http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/09/pareto-analysis

This should help you to get your results without using any loops.

[#9]

Wow guys thank you all.

I will try to implement  in a side also what what Colin suggested. This seems to be a standard and correct sollution to what i want to achieve.

Thanks once more

Julius

[#10]

Hi Again,

I have used prabhuappu example provided and now i want to have more 3 tables that in each of them i will provide data with A Flagging, B flaggin and C flagging and of course i want to use the original table where i see all my salesman with their flaggings. Can you suggest or help how to do that. All tables with 3 different flaggings have to be on the same sheet