Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Show Cust IDs having > 1 purchase in any 5 day period

Can anyone tell me how to do the following...Show IDs of customers having > 1 purchase within any 5 day period

I have a table that contains these fields: CUST_ID, UNIQUE_PURCHASE_ID, SALE_DATE

4 Replies
swuehl
MVP
MVP

It's not quite clear for which time period you want to evaluate, i.e. any 5 subsequent days in a period starting from when and ending when. I guess all of your customers qualify for your condition before they were born.

Basically, order your list of sales by customer and sale_date and calculate the difference in days between two subsequent purchases (of the same customer, and maybe taking into account the start and end dates of the total time period you want to evaluate your condition for).

If the number of days between two subsequent purchases is larger than 5, than this customer does not fulfill your requirement (there are 5 day periods possible where she doesn't purchased anything).

Create a flag for this customer, which you can use in your charts to filter your customers.

To order your list of customers, use a resident table load with ORDER BY clause.

To access the previous record (to check for same customer and previous purchase date), you can use inter record functions like Previous()  or Peek().

Counters in the Load

Frank_Hartmann
Master II
Master II

Please do not double post!

The initial thread is here:

How do I Show IDs of customers having > 1 purchase within any 5 day period.

swuehl
MVP
MVP

This thread seems to be even older than the other, maybe it has just been awaiting approval by moderation?

But in general, I agree with Frank, please try to avoid posting the same request multiple times, it clutters the forum and just makes it hard to follow a discussion.

Instead of posting multiple identical threads, add some more information to your original, single request, e.g. how your data and current model looks like, and what exactely you are expecting as a result (as I tried to mention above and when looking to the other thread, it's not clear what you expect as a result).

Not applicable
Author

Sorry.  Thanks Frank and Stefan.