Finding results for a set time period across multiple years

[jcoleman13]

Typically I run reports on our data for a full calendar year for 5 years, so that I can see the total revenue for the past 5 years.  What I need to do is to limit that by a specific date range.  I need to see the total revenue between Jan 1 and Aug 30 for the past 5 years.  I want to see 2020 - total revenue from Jan1-Aug30, 2021 - total revenue from Jan1-Aug30, and so on.

Any ideas how to do this?

[onlyshiva]

Good morning. Lets assume you are using invoice date as the filter. If you want to filter Invoice date for Jan 1st through Aug 30th for every year, for the selected Rolling 5 years you can use the below Pseudo logic

1. Find Invoice Year from invoice Date - InvoiceYear = DateTimeYear([InvoiceDateDate])

2. Find out the Range for the Year Selection

// Last completed year; keeps the window stable year-round
UpperYear = DateTimeYear(DateTimeNow()) - 1
LowerYear = [UpperYear] - 4
3. // Build per-row window bounds for that row's year (Alteryx Date is "YYYY-MM-DD" text)
WindowStart = ToString([InvoiceYear]) + "-01-01"
WindowEnd = ToString([InvoiceYear]) + "-08-30"

4. Filter  tool:

 [InvoiceYear] >= [LowerYear]
AND [InvoiceYear] <= [UpperYear]
AND [InvoiceDateDate] >= [WindowStart]
AND [InvoiceDateDate] <= [WindowEnd]

5. Summarize tool:

  Groupby - > invoice Year
Sum --> Revenue--> Total Revenue_jan1 to Aug 30

Let me know if resolves your issue

[jcoleman13]

i cant do >= to strings... should they be numbers?

[jrlindem]

Give this a try.  Most of this workflow is built out for illustrative purposes, so the initial text input and generate rows are there to build out a date span.  The first formula tool is just to add in random number values.  So ignore those parts when adapting to your use case.  The main logic you need is in the FORMULA tool i've shown below showing how you can build logic to flag dates that are between your span using Month and Day only then simply retain only TRUE values before aggregating:

Workflow also attached for reference.  Hope that helps, -Jay

[dreldrel]

You can use the filter tool to achieve a formula tool to write an if-else condition. If this helps, feel free to like the comment