topic Re: Loop through chart(straight table) and perform calculations in QlikView

Loop through chart(straight table) and perform calculations

Tue, 22 Jul 2014 06:49:41 GMT

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

Re: Loop through chart(straight table) and perform calculations

Wed, 23 Jul 2014 12:54:29 GMT

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?

Re: Loop through chart(straight table) and perform calculations

Anonymous — Wed, 23 Jul 2014 13:24:39 GMT

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 .

Re: Re: Loop through chart(straight table) and perform calculations

Wed, 23 Jul 2014 13:42:37 GMT

Hi,

This is how i want to look it like:

and this is the data from chart table

Re: Re: Loop through chart(straight table) and perform calculations

Anonymous — Wed, 23 Jul 2014 14:05:48 GMT

This is really tricky

Re: Re: Re: Loop through chart(straight table) and perform calculations

Wed, 23 Jul 2014 14:47:51 GMT

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

Re: Re: Re: Loop through chart(straight table) and perform calculations

prabhuappu — Wed, 23 Jul 2014 15:30:54 GMT

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

Re: Loop through chart(straight table) and perform calculations

Colin-Albert — Wed, 23 Jul 2014 16:30:15 GMT

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.

Re: Loop through chart(straight table) and perform calculations

Thu, 24 Jul 2014 10:45:21 GMT

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

Re: Loop through chart(straight table) and perform calculations

Thu, 28 Aug 2014 13:28:35 GMT

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

Re: Loop through chart(straight table) and perform calculations

Tue, 02 Sep 2014 08:50:27 GMT

I think i can't do it in the backend because in the front end i calculate Sum(profit) and the Sum(profit) changes dynamicaly if i select some other drill down values like date, City o some kind of products catagery.

If i calculate the values in the back end - it will not change.