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.

Function or Macro for Determining Fiscal Year and Month

DMH1
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
TheOC
15 - Aurora
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!


Bulien
271828
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)

DMH1
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

271828
8 - Asteroid

@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
7 - Meteor

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

Labels