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!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Multiple Data Fields in Cross Tab

It would be great if you could select multiple Data Fields in a single Crosstab

For example. I would like to SUM (Methodology) Pet Food Sales (Data Field 1) & Baby Food Sales (Data Field 2) By Store (Grouping Field) By Day of Week (Header field).
 

23 Comments
kane_glendenning
10 - Fireball
Hi AJ,

I think this answer may only be the solution because you simplified your question to ask it,  but the above example is able to be acheived by use of a formula tool before the cross tab. A new field "Data" = "Pet Food Sales" + "Baby Food Sales", then the new field "Data" would be used as the Data Field in the Cross Tab. Creating the extra field doesn't matter because you are about to Cross Tab it and so get rid of any fields not selected in the Cross Tab.

Thanks,
Kane
ajwilkin
7 - Meteor
Not sure I follow.

To be clear, I want two seperate columns Sum of Pet Food Sales By Store By Week and Sum of Baby Food Sales By Store By Week.

Thanks for the response.
kane_glendenning
10 - Fireball
Oh, sorry, I misunderstood. That is a situation that can cause confusion. I understand why you would like that. Until it is reviewed for implementation, I've listed below some ways that may work for you.

Currently the options are:
  1. Transpose first to get them into the same column to then crosstab,
  2. Or to run multiple crosstabs in parallel. One for each column and then join them back together using the Join Tool. So essentially, you will have your Input going to 2 crosstabs and then those both going to a Join tool as in the below screenshot.
Remember to:
  1. Untick the duplicate fields in the join tool though. 
  2. Have the same Grouping Fields selected in each CrossTab
 
mje5056
5 - Atom

As this was posted a couple of years ago, wanted to circle back and see if there was any better solution than the one above.  It seems to work, but wasn't sure if there was a more robust answer available with recent updates.

abrasch
8 - Asteroid

I'm also wondering if there is a better method to address this situation.

Willox
5 - Atom

As am I - any updates?

PaulaH
5 - Atom

I found that using Option 1 above - transpose first, get all the values into one column very helpful.  Otherwise, with Option 2, I would have to create 8 crosstabs first.  

 

Capture.PNG

CDunhill
8 - Asteroid

Thanks @PaulaH  - that saved me some head-banging on the desk. Perfect 🙂

 

I can't believe multiple value columns are not a standard feature of Cross Tab?! I must learn how to write macros to fix this once and for all!

KylieF
Alteryx Community Team
Alteryx Community Team
Status changed to: Revisit

Thank you for your idea! Our product team has reviewed this idea and cannot place it on the road map at this time. However they would still like to keep it in mind for future road map decisions. We'll update the status of this idea once we are able to term where or if we can place it on the road map.

CDunhill
8 - Asteroid

This is a pity! Hardly seems like an obscure requirement - I'd have expected this as a basic feature and something which would be frequently used?

 

But, thanks for the update nonetheless