Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Does your customer table have a date created field? This may be an easier approach.
Something like this I believe...
count({<[Minimum Transaction Date] ={'<= today() - 30'}>}[Customer Number])
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.
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.
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.
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?
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 |
Customer # | 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.