Summarize last 90 days of history for the end of each month

[memphis_data]

I have a file of millions of transactions that goes back many years. At the end of each month, I'd like to get a unique list of users that used the product within the last 90 days.  I'm doing this very manually right now by entering all of the conditions into a formula, but it feels very inefficient, and if the dataset was smaller it would be much easier to do in SQL.

transaction_ts	user_id
2024-01-01 00:04:34	123456
2024-01-11 00:04:34	987654
2024-02-03 00:04:34	666666
2024-02-07 00:04:34	123456
2024-03-01 00:04:34	777777
2024-04-01 00:04:34	666666
2024-04-12 00:04:34	123456
2024-05-01 00:04:34	987654
2024-06-01 00:04:34	777777
2024-06-16 00:04:34	777777
2024-07-03 00:04:34	123456
2024-08-07 00:04:34	987654
2024-09-04 00:04:34	444444
2024-09-11 00:04:34	777777
2024-10-02 00:04:34	777777
2024-10-15 00:04:34	987654

I would like to get the result that looks like the following without having to manual

Start Date	End Date	Unique User Count
12/31/2023	3/31/2024	4
1/30/2022	4/30/2024	3
3/2/2022	5/31/2024	3
4/1/2022	6/30/2024	4
5/2/2022	7/31/2024	2
6/2/2022	8/31/2024	3
7/2/2022	9/30/2024	4
8/2/2022	10/31/2024	3

[binuacs]

@memphis_data How do you derive the start date and the end date? also the difference between 1/30/2022 and 4/30/2024 is more than 90 days

[GrowthNatives]

Hi @memphis_data,
I see you're running into an issue to summarize last 90 days of history for the end of each month

Steps I took in Alteryx:
1. Input Data

• Use an Input Data tool with columns:

  • transaction_ts (timestamp)

  • user_id
    
    

2. Create Month-End Dates Dynamically

Use a Summarize tool:

• Group by: transaction_ts

• Then:

  • Take Min and Max of transaction_ts

Then use a Generate Rows tool: (as in picture)

Start: DateTimeTrim([Start_Date], "month")
Condition: [CurrentDate] <= DateTimeTrim([End_Date], "month")
Loop Expression: DateTimeAdd([CurrentDate], 1, "months")
Create: [CurrentDate]

Then add a Formula Tool:

      MonthEnd = DateTimeAdd(DateTimeTrim([CurrentDate], "month"), 1, "months")
      MonthEnd = DateTimeAdd([MonthEnd], -1, "days")

Now you have all month-end dates dynamically.

3. Append Month-End to Transactions

• Use an Append Fields tool:

  • Append each transaction with each End_Date
    
    

4. Filter for 90-Day Windows

Add a Filter tool:

[transaction_ts] <= [End_Date] AND
[transaction_ts] >= DateTimeAdd([End_Date], -90, "days")

5. Get Unique User Count

Use a Summarize tool:

• Group by: End_Date, user_id → to get distinct users per month

• Then again group by: End_Date and count user_id

6. Add Start Date

Add a Formula Tool to calculate:

Start_Date = DateTimeAdd([End_Date],-90,"day")

7. Optional: Sort by End_Date 

Final Result:

 

This is fully dynamic, scales with data, and you never need to update anything manually again.

Please find the workflow attached below.✨

Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀