Need help building out substitute variables for field names

[B-Boston]

Hi All,

So I have a workflow that pulls in a lot of revenue data with fields like this:

Minimum Rev Feb-22

Minimum Rev Mar-22

Minimum Rev Apr-22

Later in my workflow I'm creating fields like this to be used in some formulas each period:

Minimum Rev Current

Minimum Rev Prior

currently I go into the model monthly and update all those formulas to reference the correct month

Is there a way for me to use something at the start of the model where I tell it what the current and prior month is and have it update the rest of the formulas in the model by referencing it?

I think I might have to use some sort of formula in the field name itself, but I'm not sure.

[phottovy]

Hi @B-Boston ,

The first thing that comes to mind is using a transpose tool to pivot your data which will give you more options. In the attached workflow, I pull the month information from the field name after the transpose tool and then join with a date calculation to only keep the current and previous periods. Also, I rename the columns to more generic "Current_Period" and "Previous_Period" so your formulas do not need to be updated each time. I imagine your report is a little more complicated than this. Hopefully this points you in the right direction!

[B-Boston]

Thanks for this, yes my setup is a little more complicated.  I do not want to remove any of the other history, or even modify the name of those fields (as they are used elsewhere for trending data).  Also, transposing can get quite messy due to the amount of history and data points in here, but I may have to try that again.

I was hoping there would be a way to create a current month field with a formula and be able to reference that when selecting a field name..

so something like this.... first formula would be updated each month, but only in one spot vs many.  Second formula is how it's done today, each month I change the reference to the correct month.  Last formula is the Idea of what I hoped would work.  Maybe using an expression or something, but it would have to end up in the [ ] as a field name, and it doesn't look like Alteryx likes that.

[phottovy]

@B-Boston , 

I updated my workflow a little to show a couple other options. I don't know of a way in Alteryx to combine text to dynamically pick a field within the standard formula tool. I still think using the transpose and crosstab tools are your best option. In the updated workflow, I added several columns from your original example to hopefully get closer to your actual data.  But the main real difference is I join back to the original data set after I identified all the "current" and "prior" columns. This avoids having to hard-code the month into the formulas but still keeps all the original columns intact.

If pivoting your data is simply not an option, I included an example using the "Dynamic Select" and "Dynamic Rename" tools to isolate only the columns for the current month. You could use a similar approach for the prior month. Finally, I joined my new calculations back to my original dataset so I don't lose any of the original columns.

Hopefully these get you closer to avoiding hard-coded values in your formulas.

[B-Boston]

I'm going to give this a shot, I think it will be the best solution even if it means transforming the data and then back again.  We're working to really update and change this process anyway, so your suggestion should work well! Thanks again!