[HELP] Applying formulas to different rows in Alteryx - Macro?

[Anniefang]

Hi team, 

I've got a range of measures with each having a series of functions applied to the value of the measure. I was hoping to output the final calculation of each of the measure. 

I've attached the sample data - column C shows the value of the different measures (column A) for different periods (unit is quarter). I've broken down the functions in different columns, and the final results I'm after is column G (I've embedded functions in column G applying the formulas to illustrate). 

I've done this in excel, but I'm looking at a fairly large dataset with a lot more different functions / combinations of functions. Can this be done in one go in Alteryx? Would macros help? 

Cheers,

[GrahamL]

Hi there

Have you tried the Dynamic Replace tool? That allows you to edit the formula at run time. If not, you would need to write a formula macro to replace the expression of the formula.

Let me know how you go

Cheers

G

[Anniefang]

Thank Gilly! 

Not sure how to write this macro? Do I need to write for each single formula? 

[GrahamL · Accepted solution]

If the Dynamic Replace doesn't work for you then using a macro is fairly straightforward. You can leave the formula in the Excel sheet and then have the macro read and apply the formula. Take a look at the very simple example attached.

Let me know if this works for you.

Cheers

G

[GrahamL]

Hi there @Anniefang

Curious to know if this worked for you or gave you some ideas to try out?

Cheers

G

[Anniefang]

Hey Gilly! 

Sorry been hectic these days....

Yes the macro works, but seems only for simple calculations, i.e. if I apply one-step calc, then it works like butter! However, if say I need to apply 3 steps in total, and each of them is based on the results of the previous formula. Some of the formulas I need is "lead" or "lag", meaning I'll need to refer to a specific cell rather than a whole column...Can't see how Alteryx can refer to a single cell as what excel can do in writing up formulas? 

Any ideas or am I confusing you...

Thanks,