Pivot Table in Alteryx

SH2023

Hello,

I am new to Alteryx and suggested by Alteryx Community email to post a question on this Community Home Page "Ask Questions". Hope, this is the right place if not, please inform me.

I tried to design a workflow for data to have an output only showing how much for each Location Name having total for each Natural Account showing in the "Data" tab. (In "Data" tab, Natural Account is in 1 column. Need to be separated for different column for each natural account in the Output). I used Pivot Table Excel function and show the result/Output on the "Pivot" tab on this attached file here.

I tried to follow a sample posted in Alteryx Community, but I can't get the result as Excel Pivot Table. The Alteryx workflow shows "Issue" that I can't figure out what should be done.

Attached files: Data with Excel Pivot Table and Alteryx Workflow I created along with the Alteryx Output.

I appreciate any response.

Regards,

SH2023

GP by Location Name Workflow.yxmd

2022 Data for Pivot Table Test.xlsx

GP by Location name Pivot Output.yxdb

Help

Accepted answers

JamesCharnley

Hi @SH2023

If we want one row per location_name and one column for each natural account, we can crosstab choosing to Group By location_name, with Natural Account as our headers and amount for our values. I've also added a filter to remove the total row that would skew the values.

I've used a CreW Macro called 'Add Totals' to save myself the effort of having to make those calculations myself, you can learn more about them here: http://www.chaosreignswithin.com/p/macros.html

This doesn't match the excel pivot exactly but you could brute force headers in if you really needed it for whatever reason.

ExcelPivotCrossTab.yxzp

All comments

JamesCharnley

Hi @SH2023

I've used a CreW Macro called 'Add Totals' to save myself the effort of having to make those calculations myself, you can learn more about them here: http://www.chaosreignswithin.com/p/macros.html

This doesn't match the excel pivot exactly but you could brute force headers in if you really needed it for whatever reason.

ExcelPivotCrossTab.yxzp

SH2023

Hi James,

Thank you for the help.

It seems the CreW Macro is different tool based on the link you sent.

Is the Crew Macro tool embeded to Alteryx once you install it?

If not, how did you put "Add Totals" in the workflow canvas?

If yes, will CreW Macro be added to Alteryx menu (f.e Join, Reporting, etc on the top bar)?

JamesCharnley

Hi @SH2023

The CReW Macros are a pack of Alterx macros made by Adam Riley and @MarqueeCrew. Add Totals is just one of the useful tools within the pack. The installation instructions found on the website I linked can be followed to download them like so:

Once you've downloaded the file, extracted it to a location and run the install, you can point to the location from within User Settings -> Advanced User Settings -> Macros to add an extra palette of tools above the canvas, like so:

Once you have the tools downloaded, you can also open them up and have a look inside if you're curious how the total column and row can be made by aggregating and joining.

SH2023

Hello James,

Again, thank you very much for more explanation. I have to get an approval by the company's IT department for downloading any external software.

Hope it will be permitted and approved.

Have a great day!

Regares,

SH2023

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703