Function or Macro for Determining Fiscal Year and Month

[DMH1]

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...

[TheOC]

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!

[271828]

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)

[DMH1]

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

[271828]

@DMH1 

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

[DMH1]

Thanks Colorado970,

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