Generate all possible combinations of columns

[michael_nealey]

Imagine I have 10 dimensions and 1 measure.
The dimensions are such items as "Region", "Country", "State", "City", "Zip", "Account Manager"...the measure is sales revenue.
I'd like to create a dataset that has all possible combinations of the dimensions for the measure...
So, for example, I would want an Account Manager's (AM) individual sales revenue. I would also the AM revenue with state included, so I can compare AMs by state. I would also want the AM revenue by state, by country, by region.
Is it possible to have Alteryx determine all the possible combinations of the dimensions and then perform the calculation for the revenue by each of the combinations?

[RodL]

Michael,
I'm not clear on the question, since in a hierarchy like you describe, one value in a given level is generally only associated with another distinct value in the next level up (e.g., a particular State is always in the same Country). Not sure I understand your request for "all possible combinations of the dimensions". 
Rod

[michael_nealey]

Hi Rod (and others).

Sorry for the lack of clarity. My goal is to use the different combination of dimensions in the hiearachy to aggregate the data.
I have created an image that I hope explains this better than I can in text.
As you can see, the "Aggregated by Dimension" column shows you some of the combinations for the data on the left, in this case, some of the combinations just for the Question Category "Happiness". 
Hope this helps?

[RodL]

Thanks for clarifying. 
Assuming that you would have a fixed number of levels in your hierarchy, I would probably approach it with a sequential set of Summary tools that summarize all levels in the first tool, summarize all levels (but the next level up) in the next tool, until you have your final Summary tool that summarizes the Question Categories. Then you can Union the results together and order them in the Union tool so that the most summarized level is first and then decreasing down to the most granular level.

Here are some screenshots of a rough example I put together...

Starting data...


Process...


Ending Result...


Hope this helps.

[michael_nealey]

Thanks Rod - 
I know that the summary tool can perform the aggregation...but I'm hoping for a way to create all the many possible variations...and then perform the aggregation. Since there are so many possible variations, creating a summary tool for each one will take *forever*.
Thanks for the detailed reply and suggestion!

[RodL]

The Summarize tool will capture all of the variations that are in the data for whatever fields are selected in the 'group by' selection. So you don't really need to have a Summarize tool for each variation, only one tool for each level of the hiearchy (or basically the same number of Summarize tools as the fields you are grouping on, plus one extra for overall summary.

So for example, back to your initial question, if you have "Region", "Country", "State", "City", "Zip", and "Account Manager" as your levels, you should only need 7 Summarize tools.
The first summarization will capture every variation of those 6 fields as long as you group by all 6 columns.

[michael_nealey]

Hi Rod - thanks for the back and forth.
However, using the example in the image, I come across 11 (& counting) summaries that I would need to do using this example dataset. Starting at the most granular level of question...I see the way it plays out...but just taking out one level at a time still sees me miss a potential way of summarizing the data (i.e. Question - Type - Region, see below).
My real world problem is that I have more dimensions that I need to combine (maybe permutations is the better word).


 

 

[RodL]

Michael,
Not sure how  you would do this.
It's not really all permutations (which deals with order as well as possible values), but something like this...



...where each number represents a field (i.e., Field1, Field2, Field3, and Field4 in this case) where 'null' is also a valid option for any field at any time. If you added another field, it would add another 16 possibilities, another field would add 32 more possibilties, and so forth. 

I've thought about transposing and creating a Cartesian join (and combining it in some sort of iterative macro), but that doesn't account for the possibilities of NULL for any value.

Maybe someone else might chime in on this...

[michael_nealey]

Hi Rod -
Exactly. And I have managed to find a solution (90% Alteryx, 10% a combination / permutation generator)...although the initial combination discovery, the key part, is done using the generator.
The subsequent Alteryx work I have done involves a transpose and then a crosstab...lots of fun spent getting this to work!
Thanks so much for your involvement!
Mike

[pankaj3481]

Hi Mike,

How did you do that, could you please help me out. I have got same scenario.

Thanks,

PK