Summarize a table results in percentages

[federicohernandezd]

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 🙂

[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.

[AngelosPachis · Accepted solution]

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

[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

[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!

[TomWelgemoed]

Hi @federicohernandezd ,

Yes, thanks for the feedback - indeed, it's really good when you want to create more dynamic & scalable workflows and you don't want to hard-code your formulas (but rather just map values directly). 

Good luck on your Alteryx journey!

[AngelosPachis]

Hi @federicohernandezd ,

That's a fair question, probably that was the artefact of some testing I was doing while building the workflow.

You can ignore it and substitute it with Question ID. So the configuration of the top summarize tool should be the following

and for the one below

Happy that this solution help you understand better Transpose and Cross Tab tools and sorry if this inconvenience with that Value field confused you.

Cheers,

Angelos

[federicohernandezd]

Great!! 

Thank you very much