Forum Discussion

AllisonStephen1's avatar
AllisonStephen1
Qrew Member
4 years ago

Averaging Reports

My director wants reports showing the daily, weekly, and monthly average numbers of service tickets so he can see how busy our service team is. He wants weekends and holidays excluded from the results.

I've written a formula that calculates the number of workdays for any given time period. I've also created both chart and summary reports showing TOTAL numbers of tickets. But I can't figure out how to bring these together to show an AVERAGE number of tickets per workday/week/month.

I feel like the solution's staring me in the face, but I can't see it yet. Any suggestions?
Formulas and functions
reports and charts

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    I suggest that you make a formula checkbox field called 

    [Today is a Weekday?]

    The formula will be

    DayOfWeek([Ticket Date]) <>0
    and
    DayOfWeek([Ticket Date]) <>6

    Then just make a summary report and filter out the weekend tickets using that checkbox.

    You should be able to use the # of records twice on the summary report to show both total number and the average.
    ​

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • AllisonStephen1's avatar
      AllisonStephen1
      Qrew Member
      4 years ago
      Hi Mark! Thank you for the suggestions. I'm able to filter out the weekend tickets OK.

      Unfortunately, I can only use the # of records to show only totals (see below screenshot). I only have the option to select averages if I select a different (numeric) field from my table, but since the # of records isn't technically a table field, I'm missing that option. Am I missing something else to make the # of records also show the averages here?


      ​​​

      ------------------------------
      Allison
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        OK,
        Plan B.  Make a formula numeric field called [Number of Tickets].  Make the formula be a 1.

        Now you have a numeric field and you should be able to do Totals and Averages.

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