Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis to Determine New Customers

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.

7 Replies
Colin-Albert

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

Not applicable
Author

Something like this I believe...

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

Colin-Albert

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.

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

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

Customer #InvoiceItemInvoice DateAmount
110Banana20-Feb100
110Orange20-Feb200
110Apple20-Feb300
111Orange10-Jan150
111Apple10-Jan90
112Banana1-Jan80
112Orange1-Jan75
220Banana20-Feb60
220Apple20-Feb80
218Orange10-Feb40
218Apple10-Feb90
37Pear7-Jan80
37Pum7-Jan60

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

Customer #Min Invoice Date
11-Jan
210-Feb
37-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 #