Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nickjose7
Creator
Creator

How to apply For Loop in this problem.

Hi,

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


Data:

Dealer IDSales Qty
1100
22000
3490
4500
53000
6870
74102
8810
9920
10100
115600
12140
13635
144700
15323
16100
171790
182300
19100
206000

Requirement:

TotalABC
X204610
Y345802040210880

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


1 Solution

Accepted Solutions
Kushal_Chawda

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;

Capture.JPG

View solution in original post

13 Replies
nickjose7
Creator
Creator
Author

hic‌  Requesting help on this.

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

Anil_Babu_Samineni

Can you tell me one thing, How you are getting

          A               B          C

X        4               6            10

Y        20402     10880     3268


??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Kushal_Chawda

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%

Sergey_Shuklin
Specialist
Specialist

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.

nickjose7
Creator
Creator
Author

Dear serj_shuloveisfailkush141087‌,

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

Sergey_Shuklin
Specialist
Specialist

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.

Kushal_Chawda

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;

Capture.JPG

Sergey_Shuklin
Specialist
Specialist

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:

expr_B.png

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 😃