Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Multi-Row Forumla on many Columns

mattcoleman
8 - Asteroid

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?

6 REPLIES 6
markcurry
12 - Quasar
Aaron_Harter
11 - Bolide

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

0.PNG

 

Batch Macro Canvas:

1.PNG

mattcoleman
8 - Asteroid

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
8 - Asteroid

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
12 - Quasar

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. 

 

multirowmultifield.png

 

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
8 - Asteroid

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.

Labels