To Create Cluster column based on sales_share% for every 3 customers for every product

[Abhilash_N]

Hi All,

Iam new to qlik view i want to create cluster column

Rules to create Cluster column

Rule1: for each product minimum 3 customers  are to be cluster

Rule2: in that cluster we need to check sales_share% of each customer should be < 70% if any of customer sales_share% is >70% we need add  next customer to the existing cluster so on until we get sales_share%<70% in that cluster for customer

Rule3: in that product any of customers left unclustered i.e(one or two customers left) those customers are to be appended to the last cluster 

Input data

Products	Customers	ID	Sales
Product1	cust1	1	25260
Product1	cust2	2	16700
Product1	cust3	3	8560
Product1	cust4	4	2672
Product1	cust5	5	368
Product1	cust6	6	312
Product1	cust7	7	312
Product1	cust8	8	312
Product1	cust9	9	56
Product1	cust10	10	56
Product1	cust11	11	56
Product1	cust12	12	56
Product1	cust13	13	0
Product1	cust14	14	0
Product2	cust1	1	1,923
Product2	cust2	2	1,572
Product2	cust3	3	1,123
Product2	cust4	4	695
Product2	cust5	5	168
Product2	cust6	6	112
Product2	cust7	7	112
Product2	cust8	8	56
Product2	cust9	9	56
Product2	cust10	10	28
Product2	cust11	11	28
Product2	cust12	12	15
Product2	cust13	13	-

Output data

Products	Customers	ID	Sales	Output_culster	Sales_Share%	Avg sales
Product1	cust1	1	25260	1	50.00%	16840
Product1	cust2	2	16700	1	33.06%	16840
Product1	cust3	3	8560	1	16.94%	16840
Product1	cust4	4	2672	2	67.20%	795.2
Product1	cust5	5	368	2	9.26%	795.2
Product1	cust6	6	312	2	7.85%	795.2
Product1	cust7	7	312	2	7.85%	795.2
Product1	cust8	8	312	2	7.85%	795.2
Product1	cust9	9	56	3	25.00%	56
Product1	cust10	10	56	3	25.00%	56
Product1	cust11	11	56	3	25.00%	56
Product1	cust12	12	56	3	25.00%	56
Product1	cust13	13	0	0	0.00%	0
Product1	cust14	14	0	0	0.00%	0
Product2	cust1	1	1922.81	1	41.64%	1539.183
Product2	cust2	2	1571.93	1	34.04%	1539.183
Product2	cust3	3	1122.81	1	24.32%	1539.183
Product2	cust4	4	695	2	63.94%	271.75
Product2	cust5	5	168	2	17.23%	272
Product2	cust6	6	112	2	10.30%	272
Product2	cust7	7	112	2	10.30%	272
Product2	cust8	8	56.14	3	30.62%	36.67
Product2	cust9	9	56.14	3	30.62%	37
Product2	cust10	10	28.07	3	15.31%	37
Product2	cust11	11	28	3	15.27%	37
Product2	cust12	12	15	3	8.18%	37
Product2	cust13	13	0	0	0.00%	0

Thanks&Regards

Abhilash

[sunny_talwar]

Is this something you need to do in the script or front end?

[Kushal_Chawda]

Can you also share scenario where customer share is > 70%?

[Abhilash_N]

yes we need to do on script level

[Abhilash_N]

In the above if we see in product1 for cust4,5,6  cust4_share%=2672/(2672+368+312)=0.79 so it is >70%or>0.7 so we need to add another customers to that cluster '2' until the share% decreases to below 70%or0.7

[Kushal_Chawda · Accepted solution]

This is my attempt

Data:
LOAD
    Products,
    Customers,
    ID,
    if(Sales>0,Sales,0) as Sales
FROM [lib://Data/Data.qvd]
(qvd);

New:
Load *,
     RowNo() as Row
Resident Data
where Sales>0
Order by Products,Sales desc;

let vProduct = FieldValue('Products',1);

let vMinRow = Peek('Row',0,'New');

let vMaxRow = Peek('Row',-1,'New');

let vRowStart ='$(vMinRow)';let vRowEnd ='$(vMinRow)'+2;

let vCluster=1;

let vNextClusterEndProduct = Peek('Products','$(vRowEnd)'+2,'New');

let vNextClusterEndProductRow = Peek('Row','$(vRowEnd)'+2,'New');

let vNextRowToClusterEndProduct = Peek('Products','$(vRowEnd)','New');

let vNextRowToClusterEnd = Peek('Row','$(vRowEnd)','New');

Final:
Load * Inline [
Temp ];

do while vRowEnd <= vMaxRow

Loop:
NoConcatenate
Load ID,
     Products,
     Row,
     Sales
Resident New
where Row>='$(vRowStart)' and Row<='$(vRowEnd)';

let vCheckProduct = Peek('Products',-1,'Loop');

if '$(vCheckProduct)'<>'$(vNextRowToClusterEndProduct)' or '$(vNextRowToClusterEnd)' > '$(vMaxRow)' then

Left Join(Loop)
Load sum(Sales) as Cluster_Sales,
    Avg(Sales) as Avg_Sales
Resident Loop;

Percentage:
Load ID,
     Avg_Sales,
     Products,
     Sales,
     num(Sales/Cluster_Sales,'#0.00%') as Sales_Share
Resident Loop;

Drop Table Loop;

Concatenate(Final)
Load *,
     '$(vCluster)' as Cluster
Resident Percentage;

Drop Table Percentage;

let vProduct = '$(vCheckProduct)';

let vCluster = 1;

let vRowStart = '$(vNextRowToClusterEnd)';

let vRowEnd ='$(vNextRowToClusterEnd)'+2;

ElseIf '$(vCheckProduct)'<>'$(vNextClusterEndProduct)' or '$(vNextClusterEndProductRow)' > '$(vMaxRow)' then

NextCluster:
Load Products,
     Row
Resident New
where Products='$(vNextClusterEndProduct)' and (Row>='$(vNextClusterEndProductRow)'-3 and Row<='$(vNextClusterEndProductRow)');

let vCheckProductNextClusterRow = Peek('Row',0,'NextCluster');

let vRowEnd =if('$(vNextClusterEndProductRow)' > '$(vMaxRow)','$(vMaxRow)','$(vCheckProductNextClusterRow)'-1);

Drop Tables Loop,NextCluster;

Let vCheckProductNextClusterRow=null();

Else 

Left Join(Loop)
Load sum(Sales) as Cluster_Sales,
    Avg(Sales) as Avg_Sales
Resident Loop;

Percentage:
Load ID,
     Avg_Sales,
     Products,
     Sales,
     num(Sales/Cluster_Sales,'#0.00%') as Sales_Share,
     if(Sales/Cluster_Sales>0.7,1,0) as Flag
Resident Loop;

Drop Table Loop;

Check_Percent:
Load if(sum(Flag)>0,1,0) as Percent_Check_Flag
Resident Percentage;

let vPercent_Check = Peek('Percent_Check_Flag',0,'Check_Percent');

Drop Table Check_Percent;

if '$(vPercent_Check)' =1 then

let vRowEnd = '$(vRowEnd)'+1;

Drop Table Percentage;

else

Concatenate(Final)
Load *,
     '$(vCluster)' as Cluster
Resident Percentage;

Drop Table Percentage;

let vRowStart = '$(vRowEnd)'+1;

let vRowEnd = '$(vRowEnd)'+3;

let vCluster = '$(vCluster)'+1;

let vPercent_Check = null();

EndIf

EndIf

let vNextClusterEndProductRow = '$(vRowEnd)'+3;

let vNextClusterEndProduct = if(isnull(Peek('Products','$(vRowEnd)'+2,'New')),'$(vCheckProduct)',Peek('Products','$(vRowEnd)'+2,'New'));

let vNextRowToClusterEnd = '$(vRowEnd)'+1;

let vNextRowToClusterEndProduct = if(isnull(Peek('Products','$(vRowEnd)','New')),'$(vCheckProduct)',Peek('Products','$(vRowEnd)','New'));

Loop

Drop Tables New;

Concatenate(Final)
Load *,
     0 as Cluster,
     0 as Avg_Sales,
     0 as Sales_Share
Resident Data
Where Sales=0;

Drop Table Data;

Drop Field Temp;