topic Re: Set Analysis to Determine New Customers in QlikView

Set Analysis to Determine New Customers

Anonymous — Tue, 24 Feb 2015 20:44:52 GMT

I have a QV model that holds our sales transactions in detail. We have the customer number and all of the items purchsed on all invoices. I would like to dtermine all of the new customers in the last 30 days. So a distinct count of all customer numbers where the minimum transaction date is less than today minus 30. The set analysis formula is eluding me on this one. Any help is appreciated.

Re: Set Analysis to Determine New Customers

Colin-Albert — Tue, 24 Feb 2015 20:46:57 GMT

Does your customer table have a date created field? This may be an easier approach.

Re: Set Analysis to Determine New Customers

Tue, 24 Feb 2015 20:49:04 GMT

Something like this I believe...

count({<[Minimum Transaction Date] ={'<= today() - 30'}>}[Customer Number])

Re: Set Analysis to Determine New Customers

Anonymous — Tue, 24 Feb 2015 20:49:48 GMT

Nope. Just the transaction dates. We can have customer accounts that get created but not used. it is important to determine the activaion of the customer account and not just the creation.

Re: Set Analysis to Determine New Customers

Anonymous — Tue, 24 Feb 2015 20:51:20 GMT

I do not have a minimum transation date. All I have ar the invoice dates. I need to calculate the minimum transaction date. That is why I assumed I would need to use aggr.

Re: Set Analysis to Determine New Customers

Colin-Albert — Tue, 24 Feb 2015 21:04:46 GMT

I would suggest you derive the first order date in the load script and create a first order table containing the customer ID and first order date. You only need to generate this value once for each customer who has placed an order. You can use a where not exists clause to just add the data for new customers.

This would be more efficient than using a set expression to calculate this date in the front end.

Re: Set Analysis to Determine New Customers

Anonymous — Tue, 24 Feb 2015 21:17:14 GMT

I understand what you are saying and I will look into that approach. I also however need to derive lapsed customers. That will require me determine the max transaction date. This could make my load time balloon beyond acceptable levels. To give you an idea of the scope we are a grocery store. The fact table is on the order of 2 billion records and a few million customers. The set analysis function does not need to be exceptionally efficient and calc times of a few minutes is acceptable. This will be run in the middle of the night as part of an nPrint report.

Is there a way to do this with set analysis?

Re: Set Analysis to Determine New Customers

Anonymous — Tue, 24 Feb 2015 21:27:01 GMT

Since there seems to be a bit of confusion in terms of what my data looks like here is a representation.

Customer #	Invoice	Item	Invoice Date	Amount
1	10	Banana	20-Feb	100
1	10	Orange	20-Feb	200
1	10	Apple	20-Feb	300
1	11	Orange	10-Jan	150
1	11	Apple	10-Jan	90
1	12	Banana	1-Jan	80
1	12	Orange	1-Jan	75
2	20	Banana	20-Feb	60
2	20	Apple	20-Feb	80
2	18	Orange	10-Feb	40
2	18	Apple	10-Feb	90
3	7	Pear	7-Jan	80
3	7	Pum	7-Jan	60

I was thinking a aggr table as below would be a start

Customer #	Min Invoice Date
1	1-Jan
2	10-Feb
3	7-Jan

From there a count of the customer numbers where the date is less than today minus 30. That would return customer 2 in the above example so the count of new customers would be 1.

Customer #