Forum Discussion

SheilaAllas's avatar
SheilaAllas
Qrew Trainee
7 months ago

Formula Help - Average Productivity Per Day

I'm trying to figure out a formula to calculate average productivity per day and nothing I do seems to work.

I have a table with call reviews.  Not every person who completes reviews works every single day of the month.  I would like to determine their average daily productivity by taking the number of reviews they complete per month and dividing that by the number of days they actually worked.  

I can't seem to get the piece for the number of days that have productivity.

I could use either the Monitor Stop Date/Time field or the Date Created field to establish the days that have reviews completed.     

Any assistance is greatly appreciated.

Thanks,



------------------------------
Sheila Allas
------------------------------

3 Replies

  • You'll need to break out some kind of 'Schedule' table in your app that can join the Person with each day of the month. The idea with that table is it can act like a container for each person - and bind/summarize their activity for that particular day. You wouldn't necessarily have to manually schedule it - you could evaluate if they had ANY calls that day and treat that like a day you would consider for reporting and then evaluate productivity accordingly. 

    The setup would be a new table with essentially two fields to start - Related Person (if you have a table of people you would relate it to that table and add whatever lookup fields help) and then a field for 'Day' which would be a single date field. From there - you can either change the primary key to a string field that concatenates person with the date as a unique string, and then relate your new table to Calls, OR you can use Formula queries to do the work that a the relationship in my comment above would be doing to summarize the data. 

    The rationale is that you need to isolate individual fields for each day for that person to determine 1) if they were active and 2) evaluate their activity. This kind of setup allows you to do that and then some. 

    The above also keeps it all on a daily basis and then allows you to aggregate / report monthly. You could also pivot what I suggested, and instead do Person Months table - where instead of Days you isolate the month as a whole. You could then count the number of unique days they worked and the total calls and do your average. You lose some of the visibility in day over day trends this way since its now the month as a whole, but the end result is still the same. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • SheilaAllas's avatar
      SheilaAllas
      Qrew Trainee

      Thank you!



      ------------------------------
      Sheila Allas
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        There may be an elegant way to do this using a summary report but leveraging a Formula Query formula field.  If you can use a Formula Query to identify the first survey (lowest Record ID#) done by a particular person on a particular day and give that a value of 1, else 0  then you would be able to summarize on a summary report the number of days that they worked.

        Then obviously the summary report can summarize how many reviews they did. Then you can use the  feature in summary reports to do a "Summary Formula" to divide.

        That avoids the needs to set up and maintain additional statistical tables to keep track of how many days each individual user worked.  

        Back if you want to pursue this concept but need help with the formula query field and we can muddle through that together.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------