Count Enum similar inputs

AppSheet AppSheet Q&A
1

Hi There,

I am new to AppSheet, so I appologies if this is a simple peroblem, but I could not find anything answering my question anywhere else.

I am creating an Audit App. In this app there are several column where items are scored A, B, C, or D. For those item columns I used Enum.

Now, at the end I need to know how many A, B,C, and D there are in total in the whole form, but I am having some problem creating a formula that would count how many A, B, C, and D there are.

Does anyone have any idea how I can achieve this, or can point me in the right direction?

Thank you very much.

2

Hello @Fos23M , so you’re saying you have different columns in a same row (same form) that can have equal values and you wish to count them?

How would you like that result to be saved? is there a hard coded amount of options or is it variable?

This can be a potentially complex problem given the structure of the data, there might be better options for what you want to accomplish, you could receive some insights about it if you share more about your idea with us hehe.

1 Like
3

I made this to make it easier to explain:

ApSh1.jpg
ApSh1.jpg1613Ă—775 78.9 KB

The user would go would go through the form and score each item selecting A, B, C, or D. But, when I create a report afterward, I would like to add a PieChart with the total number of A, B, C, or D that they received during the whole audit.

If there is a better option to do this, I’m open to suggestion as long as the scoring using A,B,C,D stays in place.

I don’t honestly know if this makes it more clear than before, but I hope so.

Thanks for the help.

4

As Rafael mentioned, this is a complex problem.

If you want to keep the current data structure, I think you would have to create 4 new calculated columns, [total_A],[total_B],[total_C],[total_D], and for each one you’d have to manually count each score column with an expression like:

IFS( [col1] = "A" , 1)
+
IFS( [col2] = "A" , 1)
+
.....

Then you can chart these 4 new columns on a bar chart.

That’s a mess of work.

Another option would be to create a new Table with a single column just holding A,B,C,D in 4 separate records. Then change all of your Enum columns to Enum base-type of Ref pointing to the new Table. Then create a new column in the first Table that just adds all question columns into a List:

LIST( [col1] , [col2] , … )

Make this new column a List base-type Ref. This will create an “inline” View. You can create a chart view on the new table, pie chart type, showing the single column. Set it to ref position, and it should display as the inline view in the main table’s records.

A further option would be to completely change your data structure. Instead of having 1 column per each question, have a child table that is a single question per records. Creating the chart on this child table would be very straight forward, just like creating the pie chart from option 2.

https://help.appsheet.com/en/articles/961426-references-between-tables

3 Likes
5

Thank you very much for the help! I will definitely give a try to all the options you came up with, and see which works best for me.

Since I am here, I might as well submit you this other idea I came up with while I was playing around with the app, but I am not sure if it would make things easier. What if instead of A,B,C,D, I use colors?

ApShColor.jpg
ApShColor.jpg1593Ă—721 76.8 KB

Would this make it easier to count? Then once I have the numbers I can display them under their corresponding letter, A(purple), B(Blue), C(Green), D(Yellow).

Just trying to look at it from different angles.

6

That wouldn’t change a thing.

1 Like