Running Monthy, Quaterly, and Yearly Average

AppSheet AppSheet Q&A
, , ,
1

Hello, I’m new here, and I just started reading about creating apps for the past two weeks. I was tasked to transfer our QA sheet into Appsheet that way our employees will be able to see their scores in one place instead of accessing Google Sheets.

I was able to create QA form and it records per QA instance, which is weekly, but I am stuck on how to create a Monthly, Quarterly, and Yearly average per person. I’m able to do this in Google Sheets by means of Vlookup and it will just automatically update the Monthly, Quarterly, and Yearly averages every time a new record is added. Is this possible with Appsheet? If so, how?

Here’s a sample QA sheet.

MarcoComar_0-1727664291398.png
MarcoComar_0-1727664291398.png790×759 115 KB

Each evaluation is already categorized by Month, Quarter, Year based on the evaluation date. Do I need to create separate sheets for the MTD, QTD, and YTD average?

I know that this could be a noob question but please help.

2

In AppSheet, doing such computations using expressions could be either sync time expensive ( if done through VCs) or cumbersome to recalculate on each chage by using actions etc.

You may want to give a try to 'Group by" option in summary views.

So you could group by Person, followed by Quarter, Month and then Week. Thereafter you could use Eval_Score Average.

Control grouping in a view - AppSheet Help

Also please take a look at the following sample app from the help article Get started by using the sample apps - AppSheet Help . Please note it may not exactly answer your current query but may give you some relevant ideas.

Sales Reporting - AppSheet

2 Likes
3

Thank you! I really appreciate the samples and the group aggregate advice. I actually have that set up. One of the main reasons why I wanted to have this to be on a data sheet because I also have an employee list and I want to connect their evaluations posted in their profile and having the MTD, QTD, and YTD scores will also provide “rating” for them which is used for their Monthly, Quarterly, and Yearly performance evaluations.

I understand that I can achieve this by doing ref_rows() but I’m a bit confused on how to structure that. Do I make the Employee list as the parent and have Yearly as the eldest child who has Quarterly as her child which is Monthly’s mother that birthed Weekly? And how would the form structure look like?

4

Yes, in general, it is a good idea to set up scores as child table to an employee list table.

System generated reverse reference columns compute with lesser sync overheads.

Once you have scores captured against dates, you could aggregate those by week, month, quarter by slices etc.

1 Like