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

Question

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_tsuser_id2024-01-01 00:04:341234562024-01-11 00:04:349876542024-02-03 00:04:346666662024-02-07 00:04:341234562024-03-01 00:04:347777772024-04-01 00:04:346666662024-04-12 00:04:341234562024-05-01 00:04:349876542024-06-01 00:04:347777772024-06-16 00:04:347777772024-07-03 00:04:341234562024-08-07 00:04:349876542024-09-04 00:04:344444442024-09-11 00:04:347777772024-10-02 00:04:347777772024-10-15 00:04:34987654

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

Start DateEnd DateUnique User Count12/31/20233/31/202441/30/20224/30/202433/2/20225/31/202434/1/20226/30/202445/2/20227/31/202426/2/20228/31/202437/2/20229/30/202448/2/202210/31/20243

GrowthNatives · Answer

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! 🚀

Summarize last 90 days.yxzp

binu_acs · Answer

@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