AppSheet Aggregate Pie chart that includes rows with and without child data

AppSheet AppSheet Q&A
,
1

I have an AppSheet database with a table called Workers and a related table called Assessments. Within the Assessments table is a column called Score, which is an Enum with 5 possible options. The majority of Workers do not have any linked Assessment.

I want to create a pie chart that will show the total number of instances of each Score value for all Workers, and will also include a slice that represents all the Worker rows that have no assessment at all. I want each slice to display as a percentage of the total number of Workers in the table (NOT the total number Assessments, since I want to include unassessed workers).

I can’t figure out how to do this with the out-of-the-box Aggregate Pie Chart option in App Sheet, if I choose Assessment as the table and Score as the column for the chart, it just wants to give me a slice for each of the Enum values as a percentage of total Assessments.

I assume I need a more complex query that finds Workers with or without Assessments, and I need to create some kind of table slice on Workers and run the chart from there, but I can’t figure out how to do it.

2

The charts can only report on data that is present. You have two options and there are advantages to both::

1) Roll the score up into the Workers table and then create the chart against the Workers Table.
Advantage: No extra Assessment rows in the Assessment table. Can default to “no assessment”.
Disadvantage: You have to manage when the Score values are updated for future Assessments. No easy ability to compare assessments or flip back and forth between different assessments

2) Insert an Assessment row for each worker and report against the Assessment table.
Advantage: When there are multiple assessments or want to view past assessments, can use Slices to easy pick the assessment to view. Can compare assessments in other charts.
Disadvantage: Still need a row to represent the “no assessment” situation so there is something to display in a chart.

If you don’t already have it, I would include in the Score Enum a “no assessment” option

So, which one?

If you expect to only need to view the current assessment with no requirement to look at past reports or ability to compare, then go with option 1) as it is the easiest and most efficient implementation.

But if you need a more flexible and robust assessment reporting implementation, you will want to go with option 2).

1 Like