Pivot Dynamic Data within Groups

[TammyFoley]

I am having trouble creating a pivot with a DUAL Header and DUAL metrics.  I have attached an example. I did find one suggestion on the sight (concatenate the header columns), but I cannot get it to work when I have multiple metrics as well.  Any guidance would be appreciated.

[NicoleJohnson · Accepted solution]

See attached for a possible solution (version 11.5). Alteryx doesn't create merged cells for grids quite the same way you might do so in Excel (like the example provided), but you should be able to achieve a similar result using a combination of Transpose, a formula to concatenate the Contract Type + Column Name, and then a Cross-Tab tool to pivot the data back into columns (headers will include both contract type & column name).

Alternatively, if you are interested in exploring reporting options, there are ways that you can attach a header above your table (similar to a merged dual header cell in Excel). However, the resulting table will not be a typical table like the ones that normally output to Excel, but a table that you could then put into a formatted output document as a PDF, HTML, etc. I included a simple example in the workflow attached. 

Hope that helps point you in the right direction!

Cheers,

NJ

[TammyFoley]

Thank you.  This is extremely close, but the Total column is off.  It is adding together the Qty Shipped and the Net Sales columns as a total, rather than adding together the 5 Qty Shipped columns and the 5 Net Sales columns.  It should reflect as Total Qty Shipped and Total Net Sales.

[TammyFoley]

Using the basic logic you provided, I created one for each metric, then Joined them back together.  Doing this, I was able to get the 2 separate Total columns needed.  THANK YOU!