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

Building formulas off Text Input

azimme10
5 - Atom

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.

6 REPLIES 6
Claje
14 - Magnetar

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
5 - Atom

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
14 - Magnetar

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 useText 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 recordSame 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
17 - Castor
17 - Castor

@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
14 - Magnetar

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
5 - Atom

Thanks so much! This was very helpful :)

Labels