# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Not applicable

## Customer purchase behavior

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!

1 Solution

Accepted Solutions
MVP

Hi,

another solution might be:

```tabOrders:
[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

7 Replies
MVP

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

Not applicable
Author

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
MVP

Hi,

another solution might be:

```tabOrders:
[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

MVP

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

MVP

thanks

regards

Marco

Not applicable
Author

Thanks Marco and Swuehl!

MVP

You're welcome.

Did you get your expected result?