Creating an excel pivot in Alteryx

Hi All, I'm trying to create the attached visual in Alteryx see Output.xlsx file. I've tried using the Summarize tool along with Transpose and/or Cross tab and I can't get it to work correctly. The Test_Data.xlsx is the Input data to use. Any ideas? Thank you!

Test_Data.xlsx

Output.xlsx

Workflow

Datasets

Developer

Accepted answers

rzdodson

@TaraA so, there are a few things to unpack here. You are wanting to create an aggregation table to populate as the first table in the Excel file, followed by separate tabs for each SS Direct 1. To do this, I would recommend breaking this up in to distinct processes as detailed below.

Workflow picture.png

In the initial data preparation phase (blue container), I transposed the data to and trimmed the Name field to remove unwonted spacing.

Next, I created an aggregation table through the creation of a standard macro (orange container). Right below it, I created a batch macro process that will build the reports for each SS Direct 1 which ultimately filters out for functions that need not be accounted for before rending the tables for each SS Direct 1 (purple container).

In the final container (green), I unioned the two sections together, sorting the tables for the tab sequence you are wanting, and then rendering the entire output to a temporary Excel file.

Let me know if this is what you are looking for. If it is, please take the time to mark it as a solution to help others in their Alteryx search!

To answer your specific question, this will be the option you are looking for to separate the reports in to their own tabs:

Breaking into different tables.png

Creating Reports.yxzp

All comments

rzdodson

@TaraA are you wanting both reports to appear in the same output as shown in Sheet 1 of the Output.xlsx file, or can these be separate tabs? Can do either/or; it'll just influence the decision on which tools to use.

TaraA

@rzdodson Separate tabs by SS Direct 1, a tab for Johnson and a tab for Stevens

FinnCharlton

Hi @TaraA , Assuming you want Sheet 1, here is how to get the right format:

Cross Tab Table.yxmd

TaraA

@rzdodson @FinnCharlton do you know how to create separate tabs in the excel output by SS Direct 1, a tab for Johnson and a tab for Stevens? And only show the Functions that are related to SS Direct 1, so we don't see all those columns for each that are blank? Your help is greatly appreciated. Thank you!

rzdodson

Workflow picture.png

In the initial data preparation phase (blue container), I transposed the data to and trimmed the Name field to remove unwonted spacing.

In the final container (green), I unioned the two sections together, sorting the tables for the tab sequence you are wanting, and then rendering the entire output to a temporary Excel file.

Let me know if this is what you are looking for. If it is, please take the time to mark it as a solution to help others in their Alteryx search!

To answer your specific question, this will be the option you are looking for to separate the reports in to their own tabs:

Breaking into different tables.png

Creating Reports.yxzp

TaraA

@rzdodson thank you so much, let me review this and will let you know.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335