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

Alteryx designer Discussions

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

Selecting dynamic column names in a formula tool

Highlighted
Atom

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.  

Alteryx
Alteryx

@sccofer 

 

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.

Nebula
Nebula

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.

 

Dan

 

 

Alteryx
Alteryx

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 

 

mom.png

Atom

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.

 

 

 

Capture.JPG

 

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