Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculate Fiscal Year from a Date

Olo_DC-OCP
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! 

7 REPLIES 7
AmeliaG
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!

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

kymrundel
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!


 

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

 

RonalMarroquin
5 - Atom

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

lancejodoin
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;

nabiakter
5 - Atom

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

 

this query only returning the fiscal year in string format. how can I convert it to a datetime format fiscal date column?

Labels