PIVOT COPY FROM ONE SHEET TO ANOTHER

[amitsingh88]

Hi,

In the attached PIVOT Report file I just want to create PIVOT SUMMARY tab from PIVOT tab. Data is populated through SUMIF formula. How I can do this using Alteryx.

Thanks,

Amit Kumar

[MattBSlalom]

I think I'm missing something in your question.  It seems like your SUMIF expression is unneeded as each criteria value exists on only 1 row in your PIVOT tab.

Generally, it looks like everything you're trying to do can be easily handled through the Formula tool and the Summarize tool.

[amitsingh88]

Can you share the workflow?

I've tried this using Table toll. Will it work?

[MattBSlalom · Accepted solution]

The table tool is more for formatting your output for a report.  Before getting there, you need to do the math for your calculations.  The Pivot (source) data looks like something you created, right?  So I'll assume that incoming format is something you can control.

First of all, it looks like your value for "BRN" is incorrect as it's not including the value of "1160MOL".  With that value it should be 9900 instead of 8200.  Assuming the BRN / BUMC are just subtotals of the other values, there's no need for them in the input data at all as Alteryx can generate those easily and it will just add clutter into the workflow having to identify those input rows separately.

Secondly, it looks like BRN / BUMC are the parent groups for the other Facilities.  To simplify the workflow (since I assume you have control over that incoming format), those values should be moved to an attribute of the other rows.

Thirdly, you have 3 input tables in your Excel.  While Alteryx can import just a selection of cells, I'd suggest pushing these 3 tables together as 1 since their rows already align. 

Ideally after all the above your source data would look something like this:

FacilityGroup	FacilityID	Facility	Expected Payment	Paid with UHRIP	Discharge not Paid
BRN	1160AET	AET	5200	2040	5103
BRN	1160AMER	AMER	2000	760	1998
BRN	1160COOK	COOK	1000	400	1000
BRN	1160MOL	MOL	1700	671.2	1700
BUMC	1000MOL	MOL	320	120	312
BUMC	1000CIG	CIG	120	44	120
BUMC	1000AMER	AMER	345	136	345

Assuming all of the above, we'd load the source data, add a Formula to perform the calculations and add a Summarize to generate the Total rows.  Then use a Filter to split the data set into the 2 groups and add the Reporting tools for Text & Table with Layout tools and a Render to get all the content to output onto a single sheet.

[amitsingh88]

Very, very much Thanks for this. This will work.😊