alteryx Community

# Alteryx Designer Desktop Discussions

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

## Function or Macro for Determining Fiscal Year and Month

7 - Meteor

Is there a function or macro already developed that converts calendar date to fiscal date?  I had hoped to use a pre-existing Oracle function when writing some revenue data to a new table, but can't figure out how to make that work (yes I know - different problem!!)

My company's fiscal year starts on October 1st.  I want to create a new column in the output revenue table with the fiscal year and month (for example December 12, 2020 would yield a fiscal year/month of "202103").

I will be running the workflow in my Gallery.

Any thoughts or examples appreciated!!  I'm pulling my remaining gray hair out...

5 REPLIES 5
15 - Aurora

hey @DMH1

sorry to ask - I think this is my inexperience with Fiscal years, but whats the formatting of the fiscal year? I'm sure this is possible, just a little confused by "202103".

Cheers!

8 - Asteroid

My company uses the same fiscal year...10/1-9/30.

I have my years, months, and days in separate columns. Then I just have the following logic in a formula tool:

IF [MONTH_1] > 9

THEN [YEAR_1] + 1

ELSE [YEAR_1]

ENDIF

Obviously these will need to be formatted as numbers so you can add 1 to the year if needed. Then in another formula I create the date string:

ToString([YEAR_1], 0) +"/"+ ToString([MONTH_1], 0)+ "/" + ToString([DAY_1], 0)

7 - Meteor

Hi TheOC!

The formatting of the fiscal year is just 4-digit year.  Any calendar year with a date preceding 10/1 is the current fiscal year.  Any after 9/30 falls into our next fiscal year, or 2021.

The months begin renumbering after the start of the fiscal year, so calendar date 12/15/2020 is Year: 2021  Month: 03

Thanks for your response

8 - Asteroid

In that case, you can just have a formula for the year like I mentioned above and then a 2nd one for the month:

IF [MONTH] > 9

THEN [MONTH] - 9

ELSE [MONTH] + 3

ENDIF

7 - Meteor

I have all that logic in an Oracle function at this point.  I have to figure out a slick way to include it in my Alteryx workflow!  I was hoping to do it using IN-DB since I am processing 35 million rows daily and it runs for a long time even without adding another computation.

Thanks again!

Regards,

Dave

Labels