Discussions

 View Only
  • 1.  Formula field that calculates average values based on same date and same name of agent

    Posted 10-15-2019 14:40
    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
    ------------------------------


  • 2.  RE: Formula field that calculates average values based on same date and same name of agent

    Posted 10-15-2019 17:39
    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
    ------------------------------



  • 3.  RE: Formula field that calculates average values based on same date and same name of agent

    Posted 10-16-2019 04:48
    Edited by Ma. Isabel Gumanid 10-16-2019 04:59
    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
    ------------------------------



  • 4.  RE: Formula field that calculates average values based on same date and same name of agent

    Posted 10-16-2019 09:25
    Like this?



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



  • 5.  RE: Formula field that calculates average values based on same date and same name of agent

    Posted 10-16-2019 11:17
    Yes but 97.22% should not be showing on the last 5 rows under Weekly Quality Score as there were no data for the last 5 rows under Daily Quality Score.

    ------------------------------
    Ma. Isabel Gumanid
    ------------------------------



  • 6.  RE: Formula field that calculates average values based on same date and same name of agent

    Posted 10-17-2019 07:46
    Edited by Ma. Isabel Gumanid 10-17-2019 08:06
    Hello. Is it possible to just have in the formula field Weekly Quality Score the formula to compute for the average of Daily Quality Score per Agent per Effective Date Calculated without having to set up another table?

    ------------------------------
    Ma. Isabel Gumanid
    ------------------------------



  • 7.  RE: Formula field that calculates average values based on same date and same name of agent

    Posted 10-17-2019 10:27
    I suggest trying a summary report type by Effective Calculated Date by Agent and filtering out any invalid data.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 8.  RE: Formula field that calculates average values based on same date and same name of agent

    Posted 10-17-2019 11:14
    Indeed, I needed to create a summary report to get the average Quality score per Agent and per week. As you can see below, for week of 9/30/19, the weekly average score is 97.22%. The next thing to do is to have another column in that summary report which is a formula field (Badge Name) that compares that average value (97.22%) to another field and then display the corresponding badge name. How do we refer to that average value from the summary report in this formula field? Here is a sample formula:

    Field name: Badge Name
    Formula:
    If([Quality Score(avg)]>=[Quality Benchmark],"Excellent Quality Score"

    which means to say

    If(97.22%>=95%,"Excellent Quality Score"




    ------------------------------
    Ma. Isabel Gumanid
    ------------------------------



  • 9.  RE: Formula field that calculates average values based on same date and same name of agent

    Posted 10-17-2019 11:39
    At this time summary reports are not able to do calculations based on the values on the summary report. In other words you can't do calculations based on columns on the summary report.
    If you really do want to do that then it gets more complicated.
    You would need to set up a new table where the key to the record is the say hyphenated concatenation of the employee name and the date. You would need to have an automation to maintain that as new records get added and then do summary fields up to that record and then you would be able to do your calculation there.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------