community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

New Metric :Fiscal Year

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

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

 

 

Alteryx Certified Partner

Hey @chughes49 

 

How about this

 

Capture.PNG

Alteryx Certified Partner

@MichalM Almost the exact same solution!

Alteryx
Alteryx

Great minds think alike @LordNeilLord ?

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"?

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