Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
We have 5 years of sales data.We want to find the customer buying pattern analysis??
It is possible to find this in Qliksense???
For Example,
If customer brought a product on every Month
If customer brought a product alternate month
If customer brought a product every 3 month once
Etc
I would handle this in the loading script:
Data:
load *,
MonthStart([Invoice Date]) as InvoiceMonth,
[Customer Number]&Product as CustomerProduct,
MonthStart([Invoice Date])&[Customer Number]&Product as CustomerProductMonth;
load * Inline [
Invoice Date, Product, Customer Number, Qty
01/04/2019, A, Customer A, 10
15/04/2019, B, Customer B, 20
16/04/2019, A, Customer A, 30
17/04/2019, B, Customer A, 35
17/04/2019, A, Customer B, 5
17/04/2019, C, Customer C, 2
01/05/2019, A, Customer A, 20
01/05/2019, B, Customer B, 30
02/05/2019, C, Customer A, 25
];
Month_temp:
Load FirstValue([CustomerProductMonth]) as CustomerProductMonth , CustomerProduct, InvoiceMonth resident Data Group by CustomerProductMonth, CustomerProduct,InvoiceMonth;
Month_temp2:
LOAD CustomerProductMonth,
CustomerProduct,
InvoiceMonth,
If(CustomerProduct = Peek('CustomerProduct'), RangeSum(Peek('Orders'), 1), 1) as Orders
Resident Month_temp
Order By CustomerProduct, InvoiceMonth;
Left join (Data) Load CustomerProductMonth, Orders resident Month_temp2;
drop table Month_temp;
drop table Month_temp2;
Drop Fields CustomerProductMonth, CustomerProduct, InvoiceMonth;
try this
T1:
LOAD
"Invoice Date",
MonthName("Invoice Date") as Invoice_Month,
Product,
"Customer Number",
Qty
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);
T2:
NoConcatenate
Load *,
if(RowNo()=1 or "Customer Number"<>Previous("Customer Number") or ("Customer Number"=Previous("Customer Number") and
Product<>Previous(Product)),1,
if("Customer Number"=Previous("Customer Number") and Product=Previous(Product) and Invoice_Month<>Previous(Invoice_Month),
Peek(Orders)+1,Peek(Orders))) as Orders
Resident T1
Order by "Customer Number",Product,Invoice_Month;
Drop Table T1;
Hi,
Yes its doable with some level of coding.
Find one such example.
https://community.qlik.com/t5/New-to-QlikView/Customer-purchase-behavior/m-p/1057453
Regards,
Kaushik Solanki
Exactly I need this
Invoice Date | Product | Customer Number | Qty | Tag |
01/04/2019 | A | Customer A | 10 | 1st Purchase |
15/04/2019 | B | Customer B | 20 | 1st Purchase |
16/04/2019 | A | Customer A | 30 | 1st Purchase |
17/04/2019 | B | Customer A | 35 | 1st Purchase |
17/04/2019 | A | Customer B | 5 | 1st Purchase |
17/04/2019 | C | Customer C | 2 | 1st Purchase |
01/05/2019 | A | Customer A | 20 | 2nd Purchase |
01/05/2019 | B | Customer B | 30 | 2nd Purchase |
02/05/2019 | C | Customer A | 25 | 1st Purchase |
For Example take Customer A for the Month of April
Customer A buy a A product two time my tag as 1st Purchase
Customer A buy B Product 1 Time my tag as 1st Purchase only
For May Month
Customer A buy a A product in May Month so my tag is 2nd Purchase
Customer A buy a C product in May Month only so My tag is 1st Purchase
Etcc
Kindly do needful..
I would handle this in the loading script:
Data:
load *,
MonthStart([Invoice Date]) as InvoiceMonth,
[Customer Number]&Product as CustomerProduct,
MonthStart([Invoice Date])&[Customer Number]&Product as CustomerProductMonth;
load * Inline [
Invoice Date, Product, Customer Number, Qty
01/04/2019, A, Customer A, 10
15/04/2019, B, Customer B, 20
16/04/2019, A, Customer A, 30
17/04/2019, B, Customer A, 35
17/04/2019, A, Customer B, 5
17/04/2019, C, Customer C, 2
01/05/2019, A, Customer A, 20
01/05/2019, B, Customer B, 30
02/05/2019, C, Customer A, 25
];
Month_temp:
Load FirstValue([CustomerProductMonth]) as CustomerProductMonth , CustomerProduct, InvoiceMonth resident Data Group by CustomerProductMonth, CustomerProduct,InvoiceMonth;
Month_temp2:
LOAD CustomerProductMonth,
CustomerProduct,
InvoiceMonth,
If(CustomerProduct = Peek('CustomerProduct'), RangeSum(Peek('Orders'), 1), 1) as Orders
Resident Month_temp
Order By CustomerProduct, InvoiceMonth;
Left join (Data) Load CustomerProductMonth, Orders resident Month_temp2;
drop table Month_temp;
drop table Month_temp2;
Drop Fields CustomerProductMonth, CustomerProduct, InvoiceMonth;
try this
T1:
LOAD
"Invoice Date",
MonthName("Invoice Date") as Invoice_Month,
Product,
"Customer Number",
Qty
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);
T2:
NoConcatenate
Load *,
if(RowNo()=1 or "Customer Number"<>Previous("Customer Number") or ("Customer Number"=Previous("Customer Number") and
Product<>Previous(Product)),1,
if("Customer Number"=Previous("Customer Number") and Product=Previous(Product) and Invoice_Month<>Previous(Invoice_Month),
Peek(Orders)+1,Peek(Orders))) as Orders
Resident T1
Order by "Customer Number",Product,Invoice_Month;
Drop Table T1;