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

New Metric :Fiscal Year

chughes49
8 - Asteroid

Hey all,

 

Currently I have alot of historical data that I built a workflow around. I built a batch macro to pull in historical data and append it to one excel file. I get a new file each month. I have three date metrics already (Ex: Month=Jan, Year=2019, Month&Year= Jan 2019). I need to create a fourth metric that is Fiscal Year (ex: FY18, FY19). What kind of formula could I create where it would be ongoing so FY17, FY18, FY19, etc..? Each Fiscal year runs like this FY18= July 2018-June 2019; FY19= July 2019-June 2020. Any help would be greatly appreciated thanks!!

6 REPLIES 6
MichalM
Alteryx
Alteryx

@chughes49 

 

First, I'd turn the Monh&Year into an actual date using DateTime Parse. Once I have the date, I can extract Month number from it using DateTimeFormat formula and use the number to build out a conditional statement. Example attached.

 

IF ToNumber(DateTimeFormat([DateTime_Out], "%m")) < 7 THEN "FY"+Right(ToString([Year]-1),2)
ELSE "FY"+Right(ToString([Year]),2)
ENDIF

 

fy.png

 

 

LordNeilLord
15 - Aurora

Hey @chughes49 

 

How about this

 

Capture.PNG

LordNeilLord
15 - Aurora

@MichalM Almost the exact same solution!

MichalM
Alteryx
Alteryx

Great minds think alike @LordNeilLord ?

chughes49
8 - Asteroid

@MichalM @LordNeilLord , 

 

Thanks guys so much, I forgot to mention the data that I'm using comes over as (Ex: "2019-01-31"). I used the Datetime tool to make sure it was formatted correctly but than when I inputted the formula it gave me this. Any thoughts? I also created Year and Month with these formulas pulled from the original "2019-01-31", Would that be as to why I am getting the error " Formula: invalid type in subtraction operator"?

MichalM
Alteryx
Alteryx
You have the Year value generated as a string and you're trying to subtract from it in the formula. You can either output the variable as a number or wrap it in tonumber() formula in the IF statement.
Labels