Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
marishnagendran
Creator
Creator

Customer Buying Pattern Analysis

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

2 Solutions

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

I would handle this in the loading script:

repurchase.png

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;

 

View solution in original post

Kushal_Chawda

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;
     

 

Capture.JPG

 

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
marishnagendran
Creator
Creator
Author

Exactly I need this

Invoice DateProductCustomer NumberQtyTag
     
01/04/2019ACustomer A101st Purchase
15/04/2019BCustomer B201st Purchase
16/04/2019ACustomer A301st Purchase
17/04/2019BCustomer A351st Purchase
17/04/2019ACustomer B51st Purchase
17/04/2019CCustomer C21st Purchase
01/05/2019ACustomer A202nd Purchase
01/05/2019BCustomer B302nd Purchase
02/05/2019CCustomer A251st 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..

 

lorenzoconforti
Specialist II
Specialist II

I would handle this in the loading script:

repurchase.png

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;

 

Kushal_Chawda

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;
     

 

Capture.JPG