Multi-Row Forumla on many Columns

mattcoleman

I want to perform Multi-Row on many columns of data without having hundreds on Multi-Row tools in my workflow. I found quite a few posts on this, which all essentially say to Transpose then Cross Tab back, but every time I try to get it to work, the results of the Cross Tab look strange.

I attached a workflow with an example of exactly what I want to do (look at previous quarters data from -1Q to -4Q), but without the logic needed using the Transpose and Cross Tab (I only used multiple Multi-Row tools). Can someone please take my workflow and provide me with an example of how to do this using Transpose and Cross Tab?

Multi Row Multi Field Example.yxmd

Blake

Hi @mattcoleman! I think I was able to replicate what you were looking to do by separating the data after creating the 4 "lookback" fields. This problem is tricky because we're transforming the data in two distinct ways. Think of it as your original data and the calculated fields that we've tacked on the right side of the table. The original data is easy to deal with because we just have to crosstab the original name/value fields back to where they belong. The new fields are a bit of a challenge because we have to create new field names for our "lookback" data so we can have separate field names for each category. 

One of the nice things about Alteryx is we can solve the same problems in very different ways. I think my solution actually ended up taking one more tool than your original solution did. However, the nice thing about my solution is that it is dynamic and doesn't depend on having GDP, GBI, and Revenue as the data fields. You can add or remove fields and it will work just the same.

Either way, I'm sure someone can come along and solve this in a more graceful and succinct manner than I did. If they do, I hope to learn something new from it!

Multi Row Multi Field Example.yxmd