Easy, but you will need to setup a few things first.
1. Create a new table, call it Weeks. Make a new date field called Week Start and make a one record for 9/30. (You can make the other weeks or setup an automation to create the record each week)
2. Set the Week Start date field to be the key field for the Weeks table
3. On the table with the daily quality scores, create a new formula date field called Effective Date Formula,
FirstDayOfWeek([Effective Date])
*Make sure the first day of the week is set to Monday in the main App settings
4. Create a relationship between the two tables, Each Week has many Badges (or whatever your child table is named)
5. On the Badges table, you should have a new field called Related Week, change it to a formula date field and point it to the previous formula we created,
[Effective Date Formula]
6. On the new relationship, make a new summary field called Weekly Quality Score, select Summary of a specific field, then Average of Daily Quality Score, and add a filter to only summarize records where Daily Quality Score is not equal to 0.
7. On the new relationship, make a new lookup field called Weekly Quality Score, and you can now show that on your report!
Post back if you get stuck
------------------------------
Everett Patterson
------------------------------
Original Message:
Sent: 10-15-2019 14:40
From: Ma. Isabel Gumanid
Subject: Formula field that calculates average values based on same date and same name of agent
Hello,
How do we set up a Formula-Numeric field that calculates average of values based on same date and same name of agent? For example, in the image below, I want to capture in the field "Weekly Quality Score" the average of all Daily Quality Score for Effective Date of 09/30/19 per agent. So, for the first 5 rows, the values under "Weekly Quality Score" should be the same 97.22%.
#field #Formulasandfunctions
------------------------------
Isabel Gumanid
------------------------------