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

Dynamic select and summarise columns

Amanda_Breeze
8 - Asteroid

Hi team Alteryx,

Firstly, I apologise if this topic has been covered elsewhere but I wasn't able to find a solution that worked. 

I'm trying to get this:

Amanda_Breeze_0-1593580135803.png

To become this:

Amanda_Breeze_1-1593580177449.png

Essentially, what this is doing is creating a cross-tab of the NUMBER column, providing a sum of the values for each GROUP, then calculating the total and average and tacking them on the end. 

Trouble is, the number columns change depending on a filter (not shown) applied higher up in the workflow. I can end up with up to 12 number columns and it can even skip numbers. At the moment, I am achieving the desired output by using a summarise tool, but because it doesn't allow for dynamic selection of columns, I have to configure it each time I change the filter. 

This is the workflow as it currently exists:

Amanda_Breeze_2-1593580488129.png

I've put the containers around where dynamic column selection is required.

The formula multiplies the value in the column by the column number, which at the moment is very manual. 

I'm looking for a way of achieving the same result without having to configure both these tools.

I've attached a copy of the workflow above and the sample data.

I'd really appreciate the help.

Oh and I love the add total CReW macro.

I'm using Alteryx Designer v2020.1.5

 

Many thanks,

Amanda.

7 REPLIES 7
DavidP
17 - Castor
17 - Castor

This is how I would do it

 

DavidP_0-1593588782637.png

 

 

AngelosPachis
16 - Nebula

Hi Amanda,

 

I think I have the solution to your problem (although I didn't use the add Total crew macro).

 

Annotation 2020-07-01 082932.png

 

I added a filter tool at the beginning just to check that the workflow is dynamic.

 

22.png

 

Is this what you've been looking for?

 

Cheers

Amanda_Breeze
8 - Asteroid

Thanks @DavidP . I'll take a look at that. The average however is an accumulative average not a total average. If you look at the formula I used, you'll see what I mean.

Amanda_Breeze
8 - Asteroid

Thanks @AngelosPachis. Any chance you could share that package in Alteryx version 2020.1.5 (not the most recent). It won't open for me.

I'm keen to see what you've done.

Cheers.

PS. I only added the CReW macro cos I recently found it. I just can't believe Alteryx doesn't come with an Add Total tool out of the box. Who doesn't like to add totals?

AngelosPachis
16 - Nebula

Hi Amanda, 

 

I changed the .yxmd version to 2020.1 and it should work for you now. Please let me know if it doesn't and if that was the solution you were looking for.

 

Cheers,

Angelos

Amanda_Breeze
8 - Asteroid

Yes, perfect! So simple. You know, sometimes it's like looking at a word for too long - after a while it doesn't look right. I think that was the case with this problem. Such an elegant solution. Thank you.

AngelosPachis
16 - Nebula

I understand, sometimes all you need is just a fresh pair of eyes. Thanks for your kind words and accepting the solution. Take care.

Labels