Summarize Survey into Pivot Table

Hello!

I have a 5 question survey that I need to summarize by count and create into a pivot table. The first 4 questions have the same answers (exceeds/meets/below expectations/not observed), while the 5th question is a yes or no.

I need the output to look something like the below image.

Thank you!

Developer

Parse

Transformation

Dynamic Processing

Join

Accepted answers

DataNath

Hey @rdanella, here's one way you can do it.

1) Transpose everything into Name/Value where we have the answers and questions numbers next to each other

2) Assign a dummy value of 1 next to each record that we can sum

3) Group on the answers, using question numbers as headers and sum as the aggregation when doing the final Cross Tab

Pivot survey results.yxmd

All comments

DataNath

@rdanella can you provide some data so we can see the starting structure? Fairly simple request unless there's any unexpected transformation/cleansing you haven't mentioned.

rdanella

Of course! Below is how the file is outlined.

Team Member	Team	Q1	Q2	Q3	Q4	Q5
Person 1	Team A	Exceeds Expectations	Exceeds Expectations	Meets Expectations	Meets Expectations	Yes
Person 2	Team A	Meets Expectations	Exceeds Expectations	Meets Expectations	Exceeds Expectations	Yes
Person 3	Team A	Exceeds Expectations	Not Observed	Meets Expectations	Meets Expectations	Yes
Person 4	Team B	Exceeds Expectations	Below Expectations	Exceeds Expectations	Meets Expectations	No
Person 5	Team B	Exceeds Expectations	Meets Expectations	Below Expectations	Not Observed	No
Person 6	Team B	Meets Expectations	Meets Expectations	Below Expectations	Exceeds Expectations	Yes
Person 7	Team C	Meets Expectations	Below Expectations	Not Observed	Exceeds Expectations	No
Person 8	Team C	Meets Expectations	Exceeds Expectations	Not Observed	Below Expectations	No
Person 9	Team C	Meets Expectations	Meets Expectations	Meets Expectations	Below Expectations	No
Person 10	Team D	Below Expectations	Meets Expectations	Exceeds Expectations	Not Observed	Yes
Person 11	Team D	Below Expectations	Exceeds Expectations	Exceeds Expectations	Exceeds Expectations	Yes
Person 12	Team D	Not Observed	Exceeds Expectations	Exceeds Expectations	Exceeds Expectations	Yes

DataNath

Hey @rdanella, here's one way you can do it.

1) Transpose everything into Name/Value where we have the answers and questions numbers next to each other

2) Assign a dummy value of 1 next to each record that we can sum

3) Group on the answers, using question numbers as headers and sum as the aggregation when doing the final Cross Tab

Pivot survey results.yxmd

rdanella

this was perfect thank you!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703