community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Meteor

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,

Alteryx Certified Partner

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

Meteor

Thank Gilly! 

 

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

Alteryx Certified Partner

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

Alteryx Certified Partner

Hi there @Anniefang

 

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

 

Cheers

 

G

 

Meteor

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,

Labels