How to dynamically replace a value with another value that changes annually

[jberistain]

Example: 

FiscalYear

  FY22

  FY23

I used a formula tool to add a column for a numeric value 

ToNumber(Right([Fiscal Year], 2))

Fiscal Year   Year

  FY22            22

  FY23            23

I want to now add a column to replace "FY22" and "FY23" with "FYPrev" & "FYCurr" so I can ultimately replace them in my main data stream so when I crosstab the data (and a few other steps) I have two columns named "FYPrev_Actual" & "FYCurr_Actual" instead of "FY22_Actual" & "FY23_Actual" so I don't have to go back each year to select the new rows to cross tab.

[Armon24]

Hi @jberistain 

You might want to try and use the Dynamic Replace Tool. The Dynamic Replace tool quickly replaces data in multiple columns based on an expression or value. 

[jberistain]

I tried to do that, but I am missing the step/tool to determine which value should equal prev or curr. My fiscal year comes in as a string since it is not a date in the system of record

[Armon24]

What if you use the DateTimeNow function in Alteryx to get this year, parse it out to just get the year, and then subtract that by one to get last year. 

DateTimeNow 2022-12-09

Parsed 2022

Last Year 2021

Not sure if this helps, just thinking out loud.

[PanPP · Accepted solution]

Hi @jberistain 

I have provided a sample WF that creates FYPrev/FYCurr and FYPrev_Actual/FYCurr_Actual columns. 

Hope this helps, if it does please like this post and if it helps resolve your problem, mark it as a solution. If you have any other questions, please let us know.

[jberistain]

Thank you for this. It is exactly what I needed.