Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
hic Requesting help on this.
Note: Edited by Community Moderator to remove links to other spaces.
Can you tell me one thing, How you are getting
A B C
X 4 6 10
Y 20402 10880 3268
??
Till 17290 i got it, After that i am facing difficult to understand?
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%
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.
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
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.
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;
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 😃