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

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

Abhilash_N — Thu, 16 Jul 2020 09:22:45 GMT

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

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

sunny_talwar — Thu, 16 Jul 2020 12:47:55 GMT

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

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

Kushal_Chawda — Thu, 16 Jul 2020 16:12:32 GMT

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

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

Abhilash_N — Mon, 20 Jul 2020 06:44:45 GMT

yes we need to do on script level

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

Abhilash_N — Mon, 20 Jul 2020 09:29:41 GMT

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

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

Kushal_Chawda — Mon, 27 Jul 2020 23:17:37 GMT

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;