Alteryx designer Discussions

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

Pivot table with multiple sub groups in both rows and columns

Asteroid

My requirement is to have Report generated similar to what Excel performs over set of data containing multiple groups in the rows as well as columns and use the 'Reporting' functionality of Alteryx to deliver the output in say PDF format. 

Here is my current data set -

 

StateDemographyGradeCostQty
TelanganaLow-incomeAverage18020
TelanganaHigh-incomeGood15015
TelanganaHigh-incomeAbove Average14090
KarnatakaMid-incomeAverage13030
KarnatakaHigh-incomeAbove Average12580
KarnatakaHigh-incomeAverage19060
KarnatakaLow-incomeGood6035
KeralaLow-incomeGood9075
KeralaLow-incomeAverage4540
KeralaHigh-incomeAbove Average11015
DelhiMid-incomeGood13020

 

The output that I am looking for is similar to below -

 

Pivot Multiple Columns and Rows - Expected Output.png

 

Additionally at the end of each subgroup, I would need additional row for the subgroups say - Delhi - Total; Karnataka - Total ; Kerala - Total etc. 

How can this be achieved? I did lot of searching, I could see some posts linking to getting subgroup in the top - link. However, it doesn't have the same requirement as mine. Any help would be highly appreciated.

Highlighted
Magnetar
Magnetar

Hi @vkarthik21 

 

It's not as easy as it should be. I thought you could do this, after a little data prep, with two joined crosstabs and a Table tool (from reporting) but the headings that default in that view are totally wonky.

 

I built the pivot view from scratch, using tables and titles.

 

Incidentally, you can assign an order to the fields in the pivot by prepending a number (1,2,3) to produce the order you want. Remove them later with a RegEx_Replace in a formula tool.

Not the prettiest output, so you might play a bit more with the tables. I might too. 

 

image.pngimage.png

Highlighted
Magnetar
Magnetar

Whoops! Forgot the Render tool.

 

image.png

Highlighted
Asteroid

Hi Esther,

 

Thank you so much for the quick response. I did play around with cross tab and pivot but it wasn't resulting in the output I needed. Your solution is perfect. 


I feel Alteryx would implement this as an out-of-the box feature as this is a pretty standard reporting requirement and all the reporting tools handle it very easily.

 

Regards,

Karthik

Labels