Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jimthomlinson
Contributor III
Contributor III

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

Something like this I believe...

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

jimthomlinson
Contributor III
Contributor III
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.

jimthomlinson
Contributor III
Contributor III
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.

jimthomlinson
Contributor III
Contributor III
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?

jimthomlinson
Contributor III
Contributor III
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 #