Finding results for a set time period across multiple years

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?

Developer

Accepted answers

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

Set Time Period Across Years.yxmd

All comments

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

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

Set Time Period Across Years.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703