Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loop through chart(straight table) and perform calculations

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:

profit.png

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

1 Solution

Accepted Solutions
prabhuappu
Creator II
Creator II

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

View solution in original post

10 Replies
Not applicable
Author

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
Not applicable
Author

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 .

Not applicable
Author

Hi,

This is how i want to look it like:

Untitled.png

and this is the data from chart table

Anonymous
Not applicable
Author

This is really tricky

Not applicable
Author

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
Creator II
Creator II

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
Partner - Champion
Partner - Champion

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.

Not applicable
Author

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

Not applicable
Author

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