Don't forget to register for our panel discussion with Dean Stoecker, Amy Holland, and Mark Frisch occurring next Wednesday, June 1!

2022-05-26 Updates: Email: If you're not seeing emails be delivered from the Community, please check your spam and mark the Community emails as not junk. Thank you for your patience.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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
14 - Magnetar
14 - Magnetar

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
14 - Magnetar
14 - Magnetar

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