Summarize a table results in percentages

Hi, I'm quite new to Alteryx and have to do a report about a survey by using the software.

I'm not sure of how to summarize the data based on the answers.

Some Context:

For each question the answer goes from 0 to 10, if the value is between 0 and 6 is categorized as "BAD", if its 7 or 8 "REGULAR" and if its 9 or 10 "GOOD"

Survey ID	Question 1	Question 2
0001	6	10
0002	10	9
0003	2	7
0004	7	5

With the Formula option I created a new table with the correct label using "IF THEN ELSEIF ELSE END"

Survey ID	Question 1	Status	Question 2	Status
0001	6	Bad	10	Good
0002	10	Good	9	Good
0003	2	Bad	7	Regular
0004	7	Regular	5	Bad

Now I need to summarize the results for each question using percentages. Eg. For the Question 1, 50 % is "Bad", 25 % is "Good" and 25 % "Regular".

Which tool should I used? I tried summarize and failed... hopefully i explained myself. Thanks 🙂

Datasets

Developer

Accepted answers

AngelosPachis

Hi @federicohernandezd ,

I think you should first try to restructure your data so you have your data extending vertically and not horizontally. That means, having a single column for survey ID, a single column for the question ID and the answer in that question (1-10) and then the flag of whether it's good or bad. I believe that will help you process the data now but also in the future.

The you can use a summarize tool to count the number of answers per question and in total.

Hope that helps, let me know if you have any questions.

Regards,

Angelos

example.yxmd

All comments

echuong1

Two methods I would suggest:

Use the same method you used to assign the Good, Bad, and Regular values. Use a conditional formula to add in the numeric values.

Have a table with the corresponding numbers, and use a join.

New Workflow21.yxwz

AngelosPachis

Hi @federicohernandezd ,

The you can use a summarize tool to count the number of answers per question and in total.

Hope that helps, let me know if you have any questions.

Regards,

Angelos

example.yxmd

TomWelgemoed

Hi @federicohernandezd ,

I'm a big fan of making these kinds of problems entirely dynamic, as they often grow out of hand (i.e. the formulas get more & more complex).

They way I would do that is by having a reference table (lookup table) that contains the upper & lower boundaries of the value you wish to assign, e.g. 0 (lower value) and 6 (upper value) with the result field containing the result (Bad). That way, if new permutations come in, e.g. "above average", you can simply add it to the table.

I've taken the liberty of stealing @AngelosPachis , starting workflow (I agree with him on the transposing of rows) - but just adding the reference tables to this.

I wasn't 100% sure on how you wanted to assign values & result scores, but hopefully when you run the attached workflow, you'll understand the concept I'm referring to.

Feel free to ask questions of course!

Tom

example.yxmd

federicohernandezd

Hi @AngelosPachis, thank you very much for the reply

With your example I finally understood Transpose and Crosstab tools, also, I will take your advice of processing the data vertically.

I just have one further question, in the "Summarize" you add a column named "Value" which isn't in the options, how come? I add a screenshot of the configuration tool for "Summarize"

Thanks!

federicohernandezd

Hey @tomwelgemoed, I find this approach quite interesting. Although the result I'm trying to get is different, this workflow is really flexible. I'll keep in mind using those formulas in the filters to simplify the amount of objects. New on Alteryx but willing to learn from those tips. Thank you very much!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703