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!

Alteryx Designer Desktop Discussions

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

Adding an Excel pivot table to the right of data

NicC
7 - Meteor

Hello!

 

I searched some but couldn't find out how to add a pivot/summary to the right of my data.

 

My question is sort of two-pronged:

  1. Creating a functioning Excel pivot table using Alteryx
  2. Adding it to the right of data output

 

I've got a workflow (attached) that outputs data into separate Excel tabs based on the column 'Project Name'. I would also like to add an Excel pivot table to the right of the data on each tab that can be refreshed after I add more data. If that's not possible, I would settle for simply having the summary grouped by each 'Account Group' to the right of the data, separated by one empty column.

 

An example of what I would like as output can be found below:

 

Project NameAccount GroupMonthTotal Costs

[blank column]

Account GroupTotal
Project1Air/RailMar100 Accomodations1000
Project1Auto/LocalApr50 Air/Rail400.05
Project1Air/RailApr200.05 Auto/Local100
Project1AccomodationsApr1000   
Project1Auto/LocalApr50   
Project1Air/RailApr200   
5 REPLIES 5
ggruccio
ACE Emeritus
ACE Emeritus

Hi @NicC,

 

I think the logic you mentioned second works fine "I would settle for simply having the summary grouped by each 'Account Group' to the right of the data, separated by one empty column."

 

I've added a record ID to make sure that it maintains the original sort order after joining etc...but should work for you.

 

 

ggruccio_0-1589566600258.png

 

NicC
7 - Meteor

Thanks for getting back to me!

 

Your workflow gets me the summary but unfortunately doesn't work with the output tool I'm using. My output tool takes in a number of project names and spits out a sheet for each project name. 😕

 

Helpful to see how you pulled the summary into the sheet though!

 

Thanks,

Nic

AbhilashR
15 - Aurora
15 - Aurora

Hi @NicC, if you are okay with the approach, you can manually create and retain pivots in excel and have Alteryx just update the range of excel cells that pivot is mapped to. The attached workflow is a modification of what you already had and the excel a sample implementation of what I am referring to.

AbhilashR_0-1589591645063.png

The only catch with this approach is that the user will have to manually refresh the pivots in excel (ALT + F5 keyboard shortcut) once Alteryx writes to it.

 

I hope this helps.

AbhilashR
15 - Aurora
15 - Aurora

I ended up taking inspiration from @ggruccio's thought process and added a bunch of Reporting tools to output base data + summarized tables (pivots). 

AbhilashR_0-1589593437384.png

NicC
7 - Meteor

Thank you!

 

I think just doing the refresh in Excel will be easiest.

And thanks for sharing the different tool examples. I've not used them before, but this example will come in handy.

 

Cheers,

Nic

Labels