Alteryx Designer Discussions

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

Dynamically Reference Columns in Formula

willcogs26
6 - Meteoroid

Hello! 

 

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:

wcogswell_0-1589217171689.png

 

I have a formula tool right now that does this; 

wcogswell_2-1589217581142.png

 

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:

wcogswell_3-1589217632797.png

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!

 

Thanks!

3 REPLIES 3
fmvizcaino
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 🙂

 

fmvizcaino_0-1589233343182.png

 

Best,

Fernando Vizcaino

patrick_digan
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:

patrick_digan_0-1589284681318.png

 

willcogs26
6 - Meteoroid

This works great! Thank you!

 

Labels