Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

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
Highlighted
Valued Contributor II

Re: Customer Buying Pattern Analysis

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

Highlighted

Re: Customer Buying Pattern Analysis

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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Customer Buying Pattern Analysis

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Contributor

Re: Customer Buying Pattern Analysis

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..

 

Highlighted
Valued Contributor II

Re: Customer Buying Pattern Analysis

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

Highlighted

Re: Customer Buying Pattern Analysis

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