What's a good approach for summarizing data calculated in different columns?

[gumbatman]

Hello,

I have a little experience with Alteryx, but I cannot figure out the best approach for this problem.  

Here is an example of the already summarized data:

Year	Area	Level	Headcount	Received Bonus
2010	East	Managers	27	5
2010	North	Managers	18	3
2010	South	Managers	30	4
2010	West	Managers	18	4
2011	East	Managers	17	6
2011	North	Managers	21	10
2011	South	Managers	19	10
2011	West	Managers	16	1
2012	East	Managers	26	11
2012	North	Managers	19	9
2012	South	Managers	21	9
2012	West	Managers	30	6
2010	East	Employees	25	8
2010	North	Employees	23	11
2010	South	Employees	17	9
2010	West	Employees	30	3
2011	East	Employees	24	5
2011	North	Employees	30	2
2011	South	Employees	15	1
2011	West	Employees	27	5
2012	East	Employees	30	12
2012	North	Employees	22	4
2012	South	Employees	26	11
2012	West	Employees	28	2

What I would like to do is display the data in this format, including adding calculations for the percents:

	Employees Headcount	Employee Received Bonus	Employee Bonus Pct	Manager Headcount	Manager Received Bonus	Manager Bonus Pct
2010
East	25	8	32.0%	27	5	18.5%
North	23	11	47.8%	18	3	16.7%
South	17	9	52.9%	30	4	13.3%
West	30	3	10.0%	18	4	22.2%
2011
East	24	5	20.8%	17	6	35.3%
North	30	2	6.7%	21	10	47.6%
South	15	1	6.7%	19	10	52.6%
West	27	5	18.5%	16	1	6.3%
2012
East	30	12	40.0%	26	11	42.3%
North	22	4	18.2%	19	9	47.4%
South	26	11	42.3%	21	9	42.9%
West	28	2	7.1%	30	6	20.0%

I can kinda half do this with the Cross-tab tool, but I can't seem to recombine (Join tool?) them is a way that makes sense. This is obviously just sample data, the data I have has two additional columns that groups/splits the data further.

Thank you for the help.

[RolandSchubert · Accepted solution]

Hi,

I think, using two Cross Tab tools should solve the problem. See attached workflow, maybe you can use this approach.

[estherb47 · Accepted solution]

Hi @gumbatman 

I like @RolandSchubert 's approach, using 2 cross tabs and a join.

Another way to do this is with Transpose, Cross tab, and Formula tools.

Here, the transpose drops the Headcount & Received Bonus into rows, and a formula tool combines that with the text in Level. A crosstab builds the result you want. Some cleaning up with dynamic rename, calculate percentages, and then reorder with a select tool.

If you want the output to appear with the decimal, use this formula in the second formula tool, and be sure to set the data type as string: tostring([Employees Received Bonus]/[Employees Headcount] * 100)+"%"  Repeat for manager.

Cheers!

Esther

[gumbatman]

Esther,

Thank you for taking the time to respond. This worked perfectly for me!

[gumbatman]

Roland,

I really appreciate the great answer. Thank you so much!