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?

[markcurry]

You could try this tool from the Gallery....

https://gallery.alteryx.com/#!app/Multi-Row-Multi-Field/5da9d45e0462d7179c6316cf

[Aaron_Harter]

@mattcoleman try using a Batch Macro to loop through all of your required iterations of multi-row calculations, then use the Tile tool before Cross-Tabbing the data back into your desired output format:

Batch Macro Canvas:

[mattcoleman]

I definitely downloaded this and tried it out, but couldn't get it to work. Not sure if I'm setting it up incorrectly or not.

[mattcoleman]

While this certainly seems to work (and I honestly appreciate the effort you put into helping me) does it need to be this complex? I thought it was something as simple as adding a Record ID, Transposing, Multi-Row, Sorting, then Cross Tab back. I just can't seem to get that logic to work, which represents most of the examples I've found on Community already.

[Blake · Accepted solution]

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!

[mattcoleman]

Thanks Blake, this works perfectly! I like the fact that it is dynamic, as I will be adding and removing fields constantly.

I feel as though Alteryx should create a Multi-Row combined Multi-Field tool where you can apply Multi-Row on multiple columns. It doesn't seem that complicated, and I'm sure it would be utilized.