Pie chart with column data?

[David_Hundt]

I’m making an app for educational coaches to track how they are spending their time each week. I have the UI set up just how they want it. The coaches have sliders for 5 categories. Each Friday, they use the sliders to say proportionally how much time they spent in each category that week. It won’t accept entry until it equals 100%. Not shown: it also automatically collects the date submitted and the useremail:

So here is where I’m stuck. I would like each user to be able to see a pie chart that aggregates all of their own entries and proportionally shows how much time was spent in each category. I understand that pie charts only work with rows, not columns. I made a “helper sheet” that aggregates all entries on the spreadsheet side using simple sum functions. This shows how all the coaches spent their time as a whole, but I can’t break it out by user:

Any ideas on how I can accomplish the above graph for each user? It doesn’t even really have to be a pie chart if there is a way to do it with one of the other graphs. I’ve tried everything I can think of with different graphs and can’t get it to do what I’d like. I’m also willing to go back to the drawing board with how I’ve conceptualized the project, but the coaches really like the slider UI, and I’d like to keep that if possible.
Thanks in advance for any assistance.

[Marc_Dillon]

David_Hundt:

I made a “helper sheet” that aggregates all entries on the spreadsheet side using simple sum functions.

What does this Table look like? Is the data here split out per coach?

[Marc_Dillon]

Here is one possible solution.

Generally, to do charting in Appsheet, you need to start by formatting the data in the way that works for the chart that you want. For the chart that you want, you need a Table where there is one record per user-task combination. Like this:

For the total_time column, I’m just using random numbers in a real column for demonstration, but that should probably be a virtual column with a SUM(SELECT()) expression, in your case.

The [user] column is a Ref to a user Table.

Now, create a simple pie chart View for the aggregate Table:

I’ve set this view in ref position, because the key here, for filtering it by the user, is that I want it to display as the inline view on a user’s detail view. If you already have an inline view that you don’t want to override, let me know and I’ll show you what to do in another post.

Then we go view a user’s Detail view, et voila:

---

---

Another possible solution, is to avoid charting in Appsheet, and just create it in another platform that is better at charting, like Google Data Studio, where you probably won’t need to fiddle with creating a whole new Table like above.

[David_Hundt]

@Marc_Dillon Thank you so much for the ideas! I really like the idea of making it a ref so it shows up in the users’ detail view.

I now have a better understanding of what we need the final dataset to look like. Do you have any suggestions on how to accomplish a dataset that looks like your sample, but with only having to do one submission each week? The only way I could think of to get the dataset to work right would be to submit each category separately so they end up in rows instead of columns. So, if there were 5 categories to report on, you’d have to do one submission for category A, one for Category B, etc. That leaves room for user error to not having add up to 100%. When I made that initial UI, that was what I was shooting for - one submission and ensuring it equaled 100%.

Again, thanks so much for the detailed and actionable response.

[Marc_Dillon]

David_Hundt:

Do you have any suggestions on how to accomplish a dataset that looks like your sample, but with only having to do one submission each week? The only way I could think of to get the dataset to work right would be to submit each category separately so they end up in rows instead of columns. So, if there were 5 categories to report on, you’d have to do one submission for category A, one for Category B, etc. That leaves room for user error to not having add up to 100%. When I made that initial UI, that was what I was shooting for - one submission and ensuring it equaled 100%.

I don’t understand what you’re asking. I was not recommending you to change your existing data-entry Table. It’s all about the structure of the new aggregate Table. In my example I only had 3 task categories, you would have 5. I don’t understand how “one submission per week” would affect anything here. What exactly do you want the pie chart to show?

[David_Hundt]

Marc_Dillon:

I was not recommending you to change your existing data-entry Table. It’s all about the structure of the new aggregate Table.

Ah! I’m sorry, I misunderstood. So I would use my existing data entry and then aggregate that in the helper table like I described earlier. But the main change is that the aggregate table would be broken out by user. I think I get it now!

So, I apologize if this is a total newbie question, but my first thought is to hard-code the helper table on the back-end in my google sheet (using sumif to add up each category for each user). This is obviously not optimal when adding new users, etc. If it is not intruding too much on your time, is there a way to make that helper table and auto-populate it right in Appsheet?

[Marc_Dillon]

David_Hundt:

is there a way to make that helper table and auto-populate it right in Appsheet?

Search this community for topics about “looping”, “looping Actions”, “add any number of rows”, etc. You can run those Actions on form-save of the users table for new user.

[David_Hundt]

Thank you once again! I’ll be sure to update once I get it all figured out!

[David_Hundt · Accepted solution]

I wanted to take a moment to share how I worked this out once Marc pointed me in the right direction. I’m sure there is a fancier way to do it, but I was able to make it work they way I wanted with existing tools and my (very) limited skillset!
First, I made an action for each of the 5 time category columns that would extract the needed data and put it in it’s own row on the helper sheet using Data: add a new row to another table using values from this row.

Then I made a new action: Grouped: Execute a sequence of actions and added the 5 time category actions.

Finally, I added the new Grouped action to trigger when the slider form is submitted.

My current solution is to use slices from the helper table based on USEREMAIL to show each person’s individual graph and it works perfectly.

I may try out the earlier suggestion from @Marc_Dillon to add the graphs to the user profile as I work on it a bit more.

I’m sure there are more efficient ways to do it, and I’d be glad to hear if there are other ideas, but I’m so thankful to Marc for pointing me in the right direction to get where I am now. Cheers!