Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Date Range Filter (Fiscal Year)

AustinRiggs94
8 - Asteroid

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 

6 REPLIES 6
NicoleJohnson
ACE Emeritus
ACE Emeritus

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!

NJ

AustinRiggs94
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

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?

 

NJ

AustinRiggs94
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

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 :)

 

NJ

AustinRiggs94
8 - Asteroid

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!

Labels