Function or Macro for Determining Fiscal Year and Month

Question

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

DMH1 · Answer

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

271828 · Answer

@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 · Answer

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