Creating an excel pivot in Alteryx

[TaraA]

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!

[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:

[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 · Accepted solution]

@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.

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:

[TaraA]

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