topic Re: Function or Macro for Determining Fiscal Year and Month in Alteryx Designer Desktop Discussions

Function or Macro for Determining Fiscal Year and Month

DMH1 — Fri, 09 Oct 2020 14:59:28 GMT

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

Re: Function or Macro for Determining Fiscal Year and Month

TheOC — Fri, 09 Oct 2020 15:12:08 GMT

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!

Re: Function or Macro for Determining Fiscal Year and Month

271828 — Wed, 14 Jun 2023 23:58:50 GMT

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)

Re: Function or Macro for Determining Fiscal Year and Month

DMH1 — Fri, 09 Oct 2020 17:10:52 GMT

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

Re: Function or Macro for Determining Fiscal Year and Month

271828 — Fri, 09 Oct 2020 17:25:40 GMT

@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

Re: Function or Macro for Determining Fiscal Year and Month

DMH1 — Fri, 09 Oct 2020 17:42:03 GMT

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