Alteryx Designer Discussions

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

Dynamically Reference Columns in Formula

6 - Meteoroid



I am having issues automating my process. I currently have a process that pulls a historical YXDB and adds current month data to it, that is all automated and good to go. My issue now comes in with my calculations, as with a new month, I need to manually add the next month. 


So for example; my data is structured like this:



I have a formula tool right now that does this; 



This is where my issue comes in, being May, I now need to go in and update the process for a P4 Out_of _Stock


I'd like to have a multi-row formula do something like this:


Where if the current Field Name contains "Out_of_Stock" go find the field with the same Months Label; P1 for example, and divide by that field. 
However, as I am posting this, that formula doesn't work. I know the above formula would not work once I got to months 10 and 11, but for the sake of seeing if this is even possible, let's assume months end at 9 🙂 

This may not be possible in the way I have my data structured, but I thought if anyone could help it would be the community!


I have attached the workbook with the above tools in it!



16 - Nebula
16 - Nebula

Hi @willcogs26 ,


Unfortunatelly there is no easy way of creating formulas like you want to (similar to indirec in excel).

But using cross-tab and transpose function, we are able to achieve exactly what you want 🙂





Fernando Vizcaino

16 - Nebula
16 - Nebula

@willcogs26 First, @fmvizcaino's solution is a good one and the way I would do it as well. Second, I have an idea of adding an eval function that would be perfect for this situation. Third, I just wanted to add that the dynamic replace is another option for a use case like yours:



6 - Meteoroid

This works great! Thank you!