Customer purchase behavior

[#1]

Hi

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!

[swuehl]

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

[#3]

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

[MarcoWedel · Accepted solution]

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

[swuehl]

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

[MarcoWedel]

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

thanks

regards

Marco

[#7]

Thanks Marco and Swuehl!

[MarcoWedel]

You're welcome.

Did you get your expected result?

If so, please close this thread.

thanks

regards

Marco