Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
There's still time to register for the Q3 ACE Panel - Spatial Edition presentation tomorrow! | Need more information about the event? Check out the post here
SOLVED

Calculate Fiscal Year from a Date

Highlighted
5 - Atom

I have what I hope to be a simple question. I have a column of data that contains dates and I want to create a second columns that indicates the fiscal year in which the date resides. For example, assuming my fiscal year starts on Oct 1, a date of Nov 25, 2015 should equal Fiscal Year 2016 (FY2016). Anyone know how to do this conversion? Many thanks! 

Highlighted
Alteryx
Alteryx

Thanks for your question! I've assigned a macro I created which assigns fiscal year - you'll need to edit the underlying logic to match your specific fiscal year, but it is a great reference for the logic. 

 

Hope this helps!

Highlighted
5 - Atom

I think I found an easier way, via formula:

 

DateTimeYear(DateTimeAdd([Date Field],3,"months"))

 

[Date Field] is the field containing dates. Adding 3 months should work for fiscal years starting in October; add 2 months for fiscal years starting in November, 4 months for September, etc.

Highlighted
5 - Atom

Hey! I'm a little late to this party, but I'd love to leverage your macro.

 

Could you take a screen shot of the format for your fiscal year table that you put into an excel sheet? It doesn't get carried over when I download the macro.

 

Thanks!

 


@AmeliaG wrote:

Thanks for your question! I've assigned a macro I created which assigns fiscal year - you'll need to edit the underlying logic to match your specific fiscal year, but it is a great reference for the logic. 

 

Hope this helps!


 

Highlighted
5 - Atom

A much more elegant solution is to use a formula tool and include the following 2 formulas consecutively:

 

Create a column called "Financial Year"

 

1. IF DateTimeMonth([Date])>6 THEN DateTimeYear([Date])+1 ELSE DateTimeYear([Date]) ENDIF

 

2.IF [Financial Year]="2009" THEN "2008/2009" ELSEIF [Financial Year]="2010" THEN "2009/2010" ELSEIF [Financial Year]="2011" THEN "2010/2011" ELSEIF [Financial Year]="2012" THEN "2011/2012" ELSEIF [Financial Year]="2013" THEN "2012/2013" ELSEIF [Financial Year]="2014" THEN "2013/2014" ELSEIF [Financial Year]="2015" THEN "2014/2015" ELSEIF [Financial Year]="2016" THEN "2015/2016" ELSEIF [Financial Year]="2017" THEN "2016/2017" ELSEIF [Financial Year]="2018" THEN "2017/2018" ELSE "ERROR" ENDIF

 

You can adjust the number after the > sign if your financial year doesn't finish in June.

 

I am sure there is a better solution for formula 2 (post it if you have one please), but formula 1 already gives you the fiscal year.

 

Highlighted
5 - Atom

Query, where do you document that adding 3 and 2 is the beginning to determine the fiscal year?

Highlighted
5 - Atom

Use the following code to translate a Date into a Fiscal Year 

Note: the "- 4" is used within the following SQL because our fical year starts in April which is the fourth month in the year.

select DECODE (SIGN (TO_NUMBER (TO_CHAR (MyTable.MyDate, 'mm')) - 4),
-1, TO_NUMBER (TO_CHAR (MyTable.MyDate, 'YYYY')) - 1,
TO_NUMBER (TO_CHAR (MyTable.MyDate, 'YYYY'))) as MyFiscal

From MyTable;

Labels