Dragging formula across / Replicating formula similar to Excel

[mudith]

I have multiple columns in alteryx: Price 2019, Price 2020, Volume 2019, Volume 2020, so and so forth until 2028. Multiplying Price 2019 by Volume 2019 will give me Revenue for 2019. Similarly, multiplying Price 2020 by Volume 2020 will give me Revenue 2020. Can you please suggest how to do this using Multi Field Formula, or any better alternative?

Price 2019	Price 2020	Price 2021	Volume 2019	Volume 2020	Volume 2021	Revenue 2019	Revenue 2020	Revenue 2021
10	20	30	10	12	15	100	240	450

[OllieClarke]

Hi, @mudith, the fully dynamic way to do this, would be to pivot all those columns, break apart the name, restructure and then calculate the revenue

Once you've done this you can take a similar approach to get the data back in the original format if you want:

Hope that helps,

Ollie

[ChrisTX]

Check out the Multi-Field Formula Tool

Designer > Tools > Preparation > Multi-Field Formula Tool

https://help.alteryx.com/current/en/designer/tools/preparation/multi-field-formula-tool.html

Community > Learn > Academy > Tool Mastery > Multi-Field Formula

https://community.alteryx.com/t5/Tool-Mastery/Tool-Mastery-Multi-Field-Formula/ta-p/39658

[OllieClarke]

@ChrisTX 
How would you use the multi-field formula tool to solve this problem?

[ChrisTX]

@OllieClarke you're right.  With the changing field names, I would need to use Dynamic Replace as the primary tool.

But the Transpose / Crosstab you suggested is definitely easier to follow and avoids the complexity with multiple tools for Dynamic Select and Dynamic Rename.

 

Chris

[flying008]

Hi, @mudith 

Another way by use Dynamic Replace tool: