# 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
Did you mean:
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
MVP

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

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.

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.

MVP

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.

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?

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 # 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 #

Community Browser