Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

Selecting dynamic column names in a formula tool


Just getting started with Altryx and have a question - I have a query selecting the prior 3 months of data, I am creating a crosstab in Altryx using the dates as the column headers.  I am then creating a new column using a formula but am struggling to figure out how to do this without explicitly naming the columns?  I would like the formula to dynamically select the prior two months and subtract the  two months columns from each other.  So, [06_01_2019] - [05_01_2019]  but I don't want to have to update it each month manually.  I would imagine this is simple but I didnt find anything relevant searching for a solution.  




I'd use the Transpose tool to change the shape of the table - Column 1 would have all the dates and Column 2 all the values. I'd then use a Multi-Row Formula which allows you to build an expression by referencing values above and below the current row. You can find out more about the Multi-Row Formula in the Tool Mastery series here.


hi @sccofer 


Good solution from @MichalM.  From the description of your process it looks like you should apply the multi-row formula before you perform the cross tab.  That way the cross tabbed data will already have the formula applied to it.






Good shout @danilang . If you don't crosstab in the first place there's no need to transpose. Here's how it'd look like @sccofer 




The problem I have with not cross-tabbing the data is there are multiple data elements that will not always line up correctly when subtracting the row above.  For example when the tax type or geocode changes the row above will yield an incorrect MoM variance.  Will the group by values in the multi-row formula keep the groups together and not cause incorrect results.  So the formula will only subtract from the row above based on the group by values selected being equal?  In the sample data below when the geocode or the tax type changes for example.  I will try and experiment with it and answer my own question later today.   Much appreciate the helpful advice.






That's correct. Group by will ensure that the formula restarts whenever a new value in the group by column is identified.