topic Re: How to apply For Loop in this problem. in QlikView

How to apply For Loop in this problem.

nickjose7 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to achieve the following and need urgent help to build the logic/code for the same in Qlikview:

Data:

Dealer ID	Sales Qty
1	100
2	2000
3	490
4	500
5	3000
6	870
7	4102
8	810
9	920
10	100
11	5600
12	140
13	635
14	4700
15	323
16	100
17	1790
18	2300
19	100
20	6000

Requirement:

	Total	A	B	C
X	20	4	6	10
Y	34580	20402	10880	3298

Logic:

X(Total): Total Count of Dealer IDs.

Y(Total): Total Sales Qty

XA: Count of Top dealers who have contributed in 50% of Y(Total) i.e. 17290. (Sales QTY has to be sorted in descending order)

YA: Sum of actual Sales Qty of the dealers found in XA because they have achieved 50% sale but their actual sale is not exactly 50%.

XB: Count of NEXT Top dealers(excluding the ones counted in XA) who have contributed in next 30% of Y(Total) i.e. 10374.

YB:Sum of actual Sales Qty of the dealers found in XB because they have achieved 30% sale but their actual sale is not exactly 30%.

XC: X(Total) - (XA + XB)

YC: Y(Total) - (YA + YB)

Somebody suggested 'For Loop' should be used but I need the correct code that gives the desired result. Any help would be highly appreciated.

Thanks

Nick

Re: How to apply For Loop in this problem.

nickjose7 — Thu, 25 May 2017 08:07:48 GMT

hic‌ Requesting help on this.

Note: Edited by Community Moderator to remove links to other spaces.

Re: How to apply For Loop in this problem.

Anil_Babu_Samineni — Thu, 25 May 2017 17:50:33 GMT

Can you tell me one thing, How you are getting

A B C

X 4 6 10

Y 20402 10880 3268

Re: How to apply For Loop in this problem.

Anil_Babu_Samineni — Thu, 25 May 2017 17:51:08 GMT

Till 17290 i got it, After that i am facing difficult to understand?

Re: How to apply For Loop in this problem.

Kushal_Chawda — Thu, 25 May 2017 18:20:41 GMT

I can see only 3 top dealers are contributing to 50% of sales (descending) 17290

How you are getting 4 dealers? if so it's not 50% of total sales it's 60%

Re: How to apply For Loop in this problem.

Sergey_Shuklin — Thu, 25 May 2017 19:50:55 GMT

Hello, Nick!

I've found the solution through expressions, but it may be little complicate. So use it if you will get clear of what there is happening...

P.S.: And like other comments above I don't get how you've got 4 and 6 dealers. For me (using your condition) is obvious that under the 50% sales is 3 dealers, and next 30% of sales comes from another 3 dealers. As a result we have a six dealers that bring to our company 74.3% sales (the last cumulative sum before it get over 80%).

P.P.S: There is a way to do that with the script manipulating. But, you will lose some agile, like using variables, and you'll have to use reload for changes and so on.

Re: How to apply For Loop in this problem.

nickjose7 — Fri, 26 May 2017 06:03:12 GMT

Dear serj_shu‌ loveisfail‌ kush141087‌,

Thanks a lot for your kind efforts!!

I am sorry for the confusion:

The solution requires to Count the Top Dealers who have either matched or exceeded the 50%, next 30% and remaining 20% Sale Qty.

For example:

50% of 34580 = 17290. But the Top three dealers' sum of Qty is only 16300 which is less than 17290 therefore we have to include the next dealer also until we either match 50% or exceed it. Hence the count becomes 4 and their actual Sales Qty is 20402.

Same applies to the next 30 & remaining 20.

Hope I was clear this time.

Dear Sergey - I would like to see your solution. Kindly share the same.

Thanks

Nick

Re: How to apply For Loop in this problem.

Sergey_Shuklin — Fri, 26 May 2017 10:35:15 GMT

Hello, Nick!

I'll be glad to help. For now I don't have an opprtunity to answer, because I'm far away from my workplace. So may be few hours later I'll be able to solve your request! 😃 Hope it not confuse you much.

Re: How to apply For Loop in this problem.

Kushal_Chawda — Fri, 26 May 2017 10:38:14 GMT

Data:
LOAD * Inline [
Dealer ID Sales Qty
1 100
2 2000
3 490
4 500
5 3000
6 870
7 4102
8 810
9 920
10 100
11 5600
12 140
13 635
14 4700
15 323
16 100
17 1790
18 2300
19 100
20 6000 ](delimiter is '\t');


Group:
NoConcatenate
LOAD [Dealer ID],
     sum([Sales Qty]) as [Sales Qty Grouped]
Resident Data
Group by [Dealer ID];


Total:
LOAD sum([Sales Qty Grouped]) as TotalSales,
     Count([Dealer ID]) as TotalDealers
Resident Group;


let vTotalSales =Peek('TotalSales',0,'Total');
let vTotalDealers =Peek('TotalDealers',0,'Total');


DROP Table Total;


Accum:
LOAD *,
     if(AccumSales<= $(vTotalSales)*0.5 or (AccumSales > $(vTotalSales)*0.5 and previous(AccumSales) <= $(vTotalSales)*0.5),1) as Flag;
LOAD *,
     RangeSum([Sales Qty Grouped],Peek('AccumSales')) as AccumSales
Resident Group
Order by [Sales Qty Grouped] desc;


DROP Table Group;


Final:
NoConcatenate
LOAD *,
     if(AccumSales<= $(vTotalSales)*0.3 or (AccumSales > $(vTotalSales)*0.3 and previous(AccumSales) <= $(vTotalSales)*0.3),2) as Flag;
LOAD [Dealer ID],
     [Sales Qty Grouped],
     RangeSum([Sales Qty Grouped],Peek('AccumSales')) as AccumSales
Resident Accum
where Flag<>1
Order by [Sales Qty Grouped] desc;


Concatenate(Final)
LOAD *
Resident Accum
where Flag=1;


DROP Table Accum;


DROP Field AccumSales;

Re: How to apply For Loop in this problem.

Sergey_Shuklin — Fri, 26 May 2017 16:33:33 GMT

Hello, Nick!

Here you go - result was made with expressions.

And below you can find a little explanation of what's happenin in the expressions:

I've also added two variables for checking if the calculation is correct.

Note: The dealers and sales will be shown as the over values (as you want).

Hope you like it 😃

Re: How to apply For Loop in this problem.

nickjose7 — Sat, 27 May 2017 07:24:33 GMT

Hi Kushal,

This is awesome. Thank you so much for your quickest help.

I have checked your solution it works very well.

Nick

Re: How to apply For Loop in this problem.

nickjose7 — Sat, 27 May 2017 07:26:08 GMT

Hi Sergey,

Great solution. Works very well. Thank you so much for your kind help.

The only difficulty I now have is to decide whose solution I mark as correct because both are absolutely fine just with a difference of Script & expression level.

I am marking Kushal's answer as correct just on the basis of time otherwise both solutions are greatly appreciated.

Thanks a lot!!

Nick

Re: How to apply For Loop in this problem.

Sergey_Shuklin — Sat, 27 May 2017 07:33:19 GMT

Glad to help 😃

Re: How to apply For Loop in this problem.

Kushal_Chawda — Sat, 27 May 2017 07:59:40 GMT

That's great