Forum Discussion

Ma__IsabelGuman's avatar
Ma__IsabelGuman
Qrew Cadet
5 years ago

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
------------------------------
field
Formulas and functions

8 Replies

Sort By
  • EverettPatterso's avatar
    EverettPatterso
    Qrew Captain
    5 years ago
    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
    ------------------------------
    • Ma__IsabelGuman's avatar
      Ma__IsabelGuman
      Qrew Cadet
      5 years ago
      Thank you for this.

      Sorry, just to make it clear, I actually need to look at the "Effective Date Calculated" field which we enter data manually into. So, we have records with Effective Date Calculated that looks on a weekly basis like on 9/23/19, 9/30/19, 10/07/19 and so on. I need to look at the ones with same Effective Date Calculated then get the average of Daily Quality Score per Agent and show on the Weekly Quality Score column where in the example I showed with 09/30/19 as Effective Date Calculated then with scores ([(94.44 + 100)/2]=97.22), it should show 97.22. I will do the same thing for Daily Attendance by looking at same Effective Date Calculated then get the average of Daily Attendance per Agent and show on the Weekly Attendance column. Hope that makes sense.

      In this case, do I need to follow steps 3 and 5 from what you sent? I tried importing data to the table I created called Weeks (step 1) with fields (Project Name, Effective Date Calculated, ID, Daily Quality Score, Daily Attendance) similar to the way we import to Badge table but it needs the merge field Week Start. Am I missing something? Again, I manually import data that's only based on the start of week 9/23/19, 9/30/19, 10/07/19...

      ------------------------------
      Ma. Isabel Gumanid
      ------------------------------
      • EverettPatterso's avatar
        EverettPatterso
        Qrew Captain
        5 years ago
        Like this?



        ------------------------------
        Everett Patterson
        ------------------------------