Building formulas off Text Input

[azimme10]

Hi, 

I fairly new to Alteryx.  I am trying to build a forecasting workflow where I trend actual data over a set period of time.  I created a text input to enter the number of weeks I am forecasting, and I want to be able to reference this input in formulas to trend out data over the number of weeks entered in the text input.  How can I reference the weeks entered in the text input in the Formula tool?  Or is there another formula I should be using instead?

Thank you.

[Claje]

Hi,

Append Fields should get you the weeks so that you can reference them in a formula.  You would Append your Text Input to your main flow.

To give you more specific/granular logic I think I'd need a little bit more information about your specific use case, but that should get you started.

[azimme10]

Hi, thanks for your input!  To elaborate, I have several fields where I have formulas that read: ([Column]/4)*52 .   I'd like to have one place where I can update the 4 (current weeks in year) and 52 (total weeks in year), so that say next week, I'd update only one place to read 5 weeks, and my formulas would all update to ([Column]/5)*52.  Right now I have it entered as a text input, and hoping I can reference this text in my formulas. Is this possible?  I've uploaded some screenshots if that helps.

[Claje · Accepted solution]

Hi,

Yes this is possible :)

I've mocked up an example in two screenshots, one with a modified version of your Text Input tool (2 columns, and I renamed them) and one of the flow (I didn't write any formulas here:

Text Input tool. I moved the second value into a new column, and renamed them for ease of useSame as your flow, but I added the Append Fields tool to the workflow, which will add our new columns to each record

Once you have made these two changes, just update your formulas so that instead of using ([Column]/4)*52, you use ([Column]/[Currentweeks])*[TotalWeeks]

Essentially, replace all of your "4" values with [Currentweeks] and all of your "52" values with [TotalWeeks].

You probably don't want Currentweeks and Totalweeks in your final output.  I'd recommend using a Select tool at the end of all of this to remove these two fields.

[patrick_digan]

@azimme10 A Dynamic Replace could also accomplish this sort of dynamic formula. @Claje method is probably a better approach in this case, but the Dynamic approach could payoff if you were dealing with hundreds of fields I suppose.

[Claje]

I definitely don't use Dynamic Replace often enough! :)

Out of curiosity, whats your opinion on the method you used vs. Append +Multi-Field Formula?

EDIT: Example attached

[azimme10]

Thanks so much! This was very helpful :)