Find a date within a range using a condition

Question

Hello!!

I have a problem:

-I have 2 tables (One table is a list of IDs -users- that I have active within my platform per day and the second table is of the purchases have made those users per day).

-First table: UID (User) , day (the day refers to that day my user is still active within the platform) <--- I have this opening by day for the whole year.

-Second table: UID, Purchases, day of purchase <--- I have this opening by day for the whole year.

What I want is to find the following output and the following will be an example to make clearer what I am looking for.

Suppose I am an active user on February 10, 2022 and what I am looking for is to find the date of the last purchase I made between January 1 and February 10. THE CONDITION IS THAT I DON'T SEARCH FOR DATES AFTER FEBRUARY 10, WHICH WOULD BE MY MAXIMUM SEARCH CONIDITION.

In the same dataset I have that my user is still active on May 15 so what I am looking for is the same as in the previous paragraph... to match with the second table to find my last purchase date I made taking the range of time now between January 1 and May 15. THE CONDITION IS THAT I DON'T SEARCH FOR DATES AFTER MAY 15TH WHICH WOULD BE MY MAXIMUM SEARCH CONIDTION.

In the user dataset I will be "repeating myself on a daily basis" and what I am looking for is to find for each of those days the last purchase date taking as maximum search condition the date of the active user.

I hope my problematic is clear.

Thank you!

Luke_C · Answer

@Ever99x

Then maybe use the summarize tool on dataset 1 to find the max (latest) login per user, then join that max date to your purchases in dataset two. Then use a filter tool to filter on purchase date <= max login date.

Let me know if I'm misunderstanding - if so maybe attaching some sample data would help.

Ever99x · Answer

A user can be "active" without making purchases. A user leaves the dataset of the first table after the user does not make purchases within 30 days.

So the first table brings in itself "all the users that I must follow up so that they do not stop buying in a period between 0 and 30 days".

The "tricky part" is that I have the first table and the second table of purchases open at a daily level . If I only wanted to know the last purchase I would agree with your answer but in this case I want the last purchase date taking as filter the #UID and the day of the table 1 (not necessarily that day the user made a purchase BUT IT WOULD BE THE MAXIMUM DAY I COULD MAKE A PURCHASE...) ex. SEARCH BETWEEN JANUARY 1ST AND THE DATE OF THE DATASET IN TABLE 1)

Luke_C · Answer

Hi @Ever99x

I'm not sure how relevant the first table would be, is it possible for a user to make a purchase without logging in?

Assuming your date is stored as a date data type, use a summarize tool to group the 2nd table by UID and find the MAX date. That will be your latest purchase.