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

converting fiscal calendar to regular calendar year

ll1100000
8 - Asteroid

My company uses fiscal calendar for accounting, so the 1st month of is always February,  and it ends with next year's January.

 

For example, we are in 2017 December, but for a Fiscal calendar, we call it Fiscal year 2018 December.  The reason it is called Fiscal year 2018 is because it ends on 2018 Jan.   And Dec is the 11th month of Fiscal year 2018.

 

I have no problem to converting the fiscal month to Calendar Month_nbr and Calendar Month_Name as below, however, I can't think of a formula or a work flow for converting the fiscal year to Calendar Year...

 

Can anyone give some suggestions?   Thank you.

Fiscal YearFiscal MonthPOSCalendar YearCalendar Month_NBRCalendar Month_Name
20171238520162Feb
20172104220163Mar
20173457920164Apr
20174806020165May
20175631720166Jun
20176144920167Jul
20177592020168Aug
20178627620169Sep
201797086201610Oct
2017105413201611Nov
2017111002201612Dec
201712875620171Jan
20181106920172Feb
20182483620173Mar
20183865320174Apr
20184327920175May
20185812920176Jun
20186728120177Jul
20187935320178Aug
20188889020179Sep
201896319201710Oct
2018108039201711Nov
2018112391201712Dec
201812461320181Jan
2 REPLIES 2
cmcclellan
13 - Pulsar

Pseudo code is simply ... 

 

if [Calendar Month_NBR] = 1 

then [Fiscal Year]

else [Fiscal Year] = [Calendar Year]+1

end

 

Basically, if it's Jan then you're in the right year, otherwise add 1 

ll1100000
8 - Asteroid

@cmcclellan

Yes, that is so simple and brilliant, but I just can't think of it. 

Thank you again! 

Labels