topic Re: Customer purchase behavior in QlikView

Customer purchase behavior

Sat, 09 Jan 2016 08:35:14 GMT

I have the following data:

Date	Order ID	Customer Name	Product sub-category
1/1/2015	1	A	Computer
1/1/2015	1	A	Furniture
1/1/2015	1	A	Kitchenware
11/1/2015	2	C	Computer
12/1/2015	3	B	Furniture
12/1/2015	3	B	Kitchenware
2/2/2015	4	A	Computer
3/2/2015	5	B	Computer
4/2/2015	6	B	Furniture
4/2/2015	6	B	Kitchenware
6/2/2015	7	C	Kitchenware

I am trying to produce a chart like this in Qlikview:

I have no idea how to do it. Can anyone help? I hope that the expressions and dimensions would be dynamic (not hard coded in load script), but if not possible, do advise how it can be done via scripting too.

Do note that only customer B has a 3rd purchase, and hence, the average should not be based on 3 different customers.

Thanks!

Re: Customer purchase behavior

swuehl — Sat, 09 Jan 2016 13:49:37 GMT

Maybe like this:

Create a bar chart with no dimension and three expressions:

=Avg( Aggr( If(rowno()=1, Count(Distinct [Product sub-category])), [Customer Name], [Order ID]))

=Avg( Aggr( If(rowno()=2, Count(Distinct [Product sub-category])), [Customer Name], [Order ID]))

=Avg( Aggr( If(rowno()=3, Count(Distinct [Product sub-category])), [Customer Name], [Order ID]))

This approach requires that Order ID shows a load order correctly sorted by purchase date (like in your sample table).

Re: Customer purchase behavior

Sat, 09 Jan 2016 17:33:55 GMT

Hi,

Sorry, I realised my data is not arranged so nicely. Its more of like the following:

Date	Order ID	Customer Name	Product sub-category
1/1/2015	1	A	Computer
1/1/2015	1	A	Furniture
1/1/2015	1	A	Kitchenware
1/1/2015	2	C	Computer
12/1/2015	3	B	Furniture
12/1/2015	4	A	Kitchenware
2/2/2015	5	A	Computer
3/2/2015	6	B	Computer
4/2/2015	7	B	Furniture
4/2/2015	7	B	Kitchenware
6/2/2015	8	C	Kitchenware

I would like to eventually produce a bar chart of:

x axis: Customer Name
y axis: No. of purchases required or No. of days required

The bar chart should be sensitive to the filters of the list boxes:

Month
No. of different sub categories purchased

I have a feeling the data needs to be sorted out as shown below, but don't know how to.

Customer Name	No. of different product sub categories purchased	No. of purchases required	No. of days required
A	2	0	0
A	3	1	1
B	2	2	22
B	3	3	23
C	2	2	36
C	3	0	0

Re: Customer purchase behavior

MarcoWedel — Sat, 09 Jan 2016 19:10:06 GMT

Hi,

another solution might be:

tabOrders:
LOAD Date, 
     [Order ID], 
     [Customer Name], 
     [Product sub-category],
     AutoNumber([Order ID],[Customer Name]) as PurchaseNo
FROM [https://community.qlik.com/thread/198962] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Customer purchase behavior

swuehl — Sun, 10 Jan 2016 16:04:01 GMT

How have you calculated columns 2-4 in the 'sorted out' table, from your input table?

Re: Customer purchase behavior

MarcoWedel — Sun, 10 Jan 2016 21:27:23 GMT

please close your thread if your original question is answered and open another one for this new question.

thanks

regards

Marco

Re: Customer purchase behavior

Tue, 12 Jan 2016 16:13:34 GMT

Thanks Marco and Swuehl!

Re: Customer purchase behavior

MarcoWedel — Tue, 12 Jan 2016 23:46:12 GMT

You're welcome.

Did you get your expected result?

If so, please close this thread.

thanks

regards

Marco