Date Range Filter (Fiscal Year)

Hello All,

I am trying to filter a specific column (Time Entries) into Separate Fiscal Years. I.e., FY 18 10/1/2017 - 9/30/18 and FY 19 10/1/2018 - 9/30/2019.

I am having trouble trying to figure out what formula to use in order to separate based on these parameters.

Please see below for an example of a time entry.

Thanks for your help!

Time Entry Date
2018-08-08
2018-08-08
2018-08-08
2018-08-08
2018-06-27
2018-06-28
2018-06-28

There are about 15k time entries i will need to filter

Workflow

Accepted answers

NicoleJohnson

Try this formula!

if DateTimeMonth([Time Entry Date]) >= 10 Then DateTimeYear([Time Entry Date])+1 Else DateTimeYear([Time Entry Date]) endif

It will check the month for each of your dates, and if it's greater than 10 (i.e. 10, 11, or 12), it will use next year as the Fiscal Year, otherwise it will use the same year as the date.

Cheers!

NicoleJohnson

Sure thing.

After you create your Fiscal Year field, add another formula called "Filename" and do something like the following:

"C:\Folder Location\Fiscal Year " + ToString([Fiscal Year]) + " Output.xlsx|||Sheet1"

In this example, the C:\Folder Location part would be whatever filepath is needed to get to the folder where you want it saved... and then you can create the name of your file, using the Fiscal Year as part of it (may need to use ToString to convert your year to text, depending on the data type)... and then make sure you end it with ||| three pipe delimiters & a sheet name for Excel.

If you'd like some more info on the Output tool & different ways you can configure it, check out this Output Data Tool Mastery article.

As for the warning you are getting, make sure that the file you are trying to write to is closed - Alteryx cannot read or write to a file that is open

All comments

NicoleJohnson

Try this formula!

if DateTimeMonth([Time Entry Date]) >= 10 Then DateTimeYear([Time Entry Date])+1 Else DateTimeYear([Time Entry Date]) endif

It will check the month for each of your dates, and if it's greater than 10 (i.e. 10, 11, or 12), it will use next year as the Fiscal Year, otherwise it will use the same year as the date.

Cheers!

AustinRiggs94

The formula makes sense, but how would i split it into FY 19 and FY 18 (two separate reports)

Thank you for your help!

NicoleJohnson

Ah yes, ok so once you have your Fiscal Year column, you can use that as a value to split up your results into different tabs in an Excel workbook (check the box in the Output tool to Change File/Table Name and then select your Fiscal Year field... this will create a tab called "2018", "2019", etc. based on the results of that formula). If you want them in separate files altogether, you just need an additional formula that creates a filename (will need full filepath where you want the results output) and then you can choose the option to Replace Entire Filepath in your output tool, instead of Change File/Table Name.

Does that help?

AustinRiggs94

That helps significantly. I am going to have to separate them into different files because i will using them later on in the workflow. Do you know what additional formula i would need? Sorry never have created a new output based on a field.

I also was getting an error (when i ran it a second time, first time worked) of "Unable to Open archive for zipping" when i selected Change File/Table Name

Please let me know and thank you for your help.

NicoleJohnson

Sure thing.

After you create your Fiscal Year field, add another formula called "Filename" and do something like the following:

"C:\Folder Location\Fiscal Year " + ToString([Fiscal Year]) + " Output.xlsx|||Sheet1"

If you'd like some more info on the Output tool & different ways you can configure it, check out this Output Data Tool Mastery article.

As for the warning you are getting, make sure that the file you are trying to write to is closed - Alteryx cannot read or write to a file that is open

AustinRiggs94

That worked! I believe for now this is all i needed, now i can separate my workflow into fiscal years. Thank you so much for your help!

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335